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

[vinieme12]

try

vMaxFiscalMonth=  =Max(TOTAL FiscalMonth)   (should have an equal sign in the variable definition)

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

[deniscamh]

Thanks for replay Vineeth,

Your solution works fine with the past years at the last month for theses years is 12 but it would not work for current year as the last month for current year is 8.

Also it does not work for Quarters as i need last moth for each quarter.

Do you have any other ideas?

Thanks

[vinieme12]

For CurrentYear

vMaxFiscalMonth=  =Max({<YearField = {$(=Year(today())) }>}TOTAL FiscalMonth)

Also it does not work for Quarters as i need last moth for each quarter.

     Create this flag in a MasterCalendar

[agigliotti]

maybe this:

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

[deniscamh]

Hi Andrea,

Thank you for replay

It works partially.

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.

Do you have any ideas of how to do that.

[agigliotti]

let's try with the below expression:

if( FiscalMonth = Max(FiscalMonth), count(distinct Donors) )

[deniscamh]

Thanks Vineeth, but I cannot create separate variable for current year I need one for all fiscal years as I am using the same dimension in pivot table.

[deniscamh]

In that case it would work only if you select a month otherwise the expression would not give any result...

[sunny_talwar]

May be try this

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