Hi All! I'm hoping someone can help me with a dilemma I'm running into. I have been tasked with creating Monthly Metrics Reports for our gift officers which will include the number of Asks made and the number of plans closed (Written Pledge or Gift Received) in the previous month, along with the Ask Amounts and Contribution and/or Recorded Amounts tied to those plans. I've been able to pull together a Pivot Table showing the number of Solicitation steps and Closed steps that have a Completed Date falling in the previous month, the issue I'm facing is pulling in the amounts while maintaining the Completed Date filter.
So when I pull the number of Solicitation Steps and the Ask Amounts associated with the plans (with a filter to only pull plans that have a solicitation step with a Completed Date Last Month) I get zeroes in the Ask Amount fields (screenshot below). However, if I remove the filter the Ask Amounts populate, but now it pulls in plans for anyone who has an Ask Amount, even if the plan didn't have a Solicitation step completed last month (screenshot below). I've tried going into the formula of the Ask Amount values and filter by Step Type and Completed Date there, but then the amount reverts to zero.
Right now I can work around it by pulling the list without the filter and simply sort by who has a value in the Asks column, but if anyone knows of a better way I am very open to suggestions! Also, I have tried to track this information separately in an Indicator widget, but again once I had the Step Type and Completed Date filters I get zero (and if I don't add them it pulls all Ask Amounts). Thanks!
With Filter:
Without Filter: