I have a pivot table that has Current donor status as a Row Dimension and Fiscal year, Fiscal Quarter and Fiscal Month as Column Dimension. The measure is number of donors, simple expression : count(distinct Donors). Please see screenshot below.
What I am trying to do is for each Fiscal Year instead of showing full year results to show only Last Month result for that Fiscal year and the same for Fiscal Quarter.
What i did so far is created variable vMaxFiscalMonth = Max(FiscalMonth) and added it to expression:
But the result is still for full year instead of Last Month.If I hard code it, means put 12 instead of variable it works but I cannot hard code it as I need it to be adjusted according to Fiscal year and Fiscal Quarter.
Can anyone let me what I am missing in my expression or any other solution?
When I add the expression to the pivot table it does not display current year as it does not have month 12 but it would display it when you select it. I need it to be displayed as well according to last month for this year results.
Also only Q4 is displayed unless you select specific Quarter. I need all Quarters to be displayed with the results of last month for that quarter.