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

[agigliotti]

maybe this:

if( Max(FiscalMonth) = Max( TOTAL <FiscalYear> FiscalMonth), count(distinct Donors) )

[deniscamh]

Hi Sunny,

Unfortunately it does not give me the correct results.

Please see below

[sunny_talwar]

How about this

FirstSortedValue(Aggr(Count(DISTINCT Donors), [Donor Status], [Fiscal Year], [Fiscal Quarter], [Fiscal Month]), -Aggr([Fiscal Month], [Donor Status], [Fiscal Year], [Fiscal Quarter], [Fiscal Month]))

[deniscamh]

Hi Sunny,

It look like it works

My only concern why sometimes it does not display data (look on screenshot)?

How ever if I expand the Fiscal Quarter and Fiscal Month id displays everything.

Without expanding:

After Expanding:

[sunny_talwar]

May be try using a DISTINCT....

FirstSortedValue(DISTINCT Aggr(Count(DISTINCT Donors), [Donor Status], [Fiscal Year], [Fiscal Quarter], [Fiscal Month]), -Aggr([Fiscal Month], [Donor Status], [Fiscal Year], [Fiscal Quarter], [Fiscal Month]))

[deniscamh]

Still does not work for 2017-2018, but worked for 2016-2017 though.

Also the totals are incorrect.

[sunny_talwar]

May be you have Fiscal Month in 2017-2018 with no Donor information?

[sunny_talwar]

You can try this

FirstSortedValue({<Donor = {'*'}>} DISTINCT Aggr(Count(DISTINCT Donors), [Donor Status], [Fiscal Year], [Fiscal Quarter], [Fiscal Month]), -Aggr(Only({<Donor = {'*'}>}[Fiscal Month]), [Donor Status], [Fiscal Year], [Fiscal Quarter], [Fiscal Month]))

[deniscamh]

no i have for every month,

If I expand the year it works.

[sunny_talwar]

What do you get when you use Max([Fiscal Month]) in this chart?