Display Results based on Last Month

[deniscamh]

Hi all,

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:

count({<FiscalMonth = {"=$(vMaxFiscalMonth)"}>} distinct Donors).

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?

Thanks

Denis

[sunny_talwar]

Alright, lets check this

FirstSortedValue(DISTINCT Aggr(Count(DISTINCT [Constituent ID]),Month, FiscalYear,FiscalQuarter,[Curent Donor Status]),

-Aggr(Month,FiscalYear,FiscalQuarter,[Curent Donor Status], Month))

Replaced FiscalMonth with Month field in the Aggr() function's dimension

[deniscamh]

Hey Sunny,

It looks like the issue depends on the order you open expand and collapse the pivot fields.

You said there can be other solution that FirstSortedValue may be we can try it?

[sunny_talwar · Accepted solution]

Here is the other option

If(SecondaryDimensionality() = 1,

Sum(Aggr(If(Month = Max(TOTAL <FiscalYear, [Curent Donor Status]> Month), Count(DISTINCT [Constituent ID])), Month, FiscalYear, [Curent Donor Status])),

If(SecondaryDimensionality() = 2,

Sum(Aggr(If(Month = Max(TOTAL <FiscalYear, [Curent Donor Status], FiscalQuarter> Month), Count(DISTINCT [Constituent ID])), Month, FiscalYear, [Curent Donor Status], FiscalQuarter)),

If(SecondaryDimensionality() = 3,

Sum(Aggr(If(Month = Max(TOTAL <FiscalYear, [Curent Donor Status], FiscalQuarter, FiscalMonth> Month), Count(DISTINCT [Constituent ID])), Month, FiscalYear, [Curent Donor Status], FiscalQuarter, FiscalMonth)))))

[deniscamh]

Hi Sunny,

That solution is works

The only thing I changed is Month to FiscalMonth as my Fiscal Year starts in April and ends in March so if i put Month it gives me December results instead of March.

And as a dimension I added Month instead of Fiscal Month

Other than that it work.s perfect.

Thank you so much for your help

If(SecondaryDimensionality() = 1,

Sum(Aggr(If(FiscalMonth= Max(TOTAL <FiscalYear, [Curent Donor Status]> FiscalMonth), Count(DISTINCT [Constituent ID])), FiscalMonth, FiscalYear, [Curent Donor Status])),

If(SecondaryDimensionality() = 2,

Sum(Aggr(If(FiscalMonth= Max(TOTAL <FiscalYear, [Curent Donor Status], FiscalQuarter> FiscalMonth), Count(DISTINCT [Constituent ID])), FiscalMonth, FiscalYear, [Curent Donor Status], FiscalQuarter)),

If(SecondaryDimensionality() = 3,

Sum(Aggr(If(Month = Max(TOTAL <FiscalYear, [Curent Donor Status], FiscalQuarter, FiscalMonth> FiscalMonth), Count(DISTINCT [Constituent ID])), FiscalMonth, FiscalYear, [Curent Donor Status], FiscalQuarter, FiscalMonth)))))

[sunny_talwar]

That is just great... I usually prefer to use FirstSortedValue(), but not sure why it acted so weird.... but I am glad we managed to get this resolved

[deniscamh]

Thanks Sunny,

You can not imagine how happy am I!  You did a great job and your help is much appreciated!

Thanks again.