Need help with set analysis in a table

[agrawalpavan]

Hi all,

thanks in advance for your help.

There is a table with Date, month and year and an amount field called "RevAmount" and a master calendar associated with it.  There is one dimension field called "Salesperson".

Created a "Table" chart with these fields.

I need to refer to sum(RevAmount) of previous month on this table.

For example if I have filter pane selected to year 2018 and the table is showing SUM(RevAmount) by Month, I have 12 rows one for each month (for year 2018). Now On each row I need to refer to SUM(RevAmount) for previous month.  For 2018 January I need to get the value for 2017 December which is not part of the selection (Year=2018).

I tried using _MonthSerial and that is not working.

This formula is not working way I want.

SUM({1<Year=,Month=,[_MonthSerial]=,[_MonthSerial] = {"=$(=Max([_MonthSerial])-1)"}  >}  RevAmount)

[sunny_talwar · Accepted solution]

Try this instead

Aggr(Above(Sum({<Year, Month, [_MonthSerial], [_MonthSerial]>} RevAmount)), MonthYear)

[agrawalpavan]

Sunny,

This is awesome. It works.

Really appreciate it.

Thank you,

Pavan