Previous Year in Set Analysis calculation

[ngulliver]

Hi,

I currently have an expression:

=num(sum({<YearMonth={"$(vMaxYearMonth)"},KPI = {[Overheads]}>} Amount),'#,##0')

where $(vMaxYearMonth is being used to limit the month to no greater than today:

=Date(addmonths(max({<[YearMonth]*={'<=$(=Date(today(), 'YYYYMM'))'}>}[YearMonth]),0), 'YYYYMM')

I am trying to create a similar expression to view the same month for the previous year. However, every attempt I have made has not worked. I believe I should be using AddMonths function but I cannot seem to get it to work within this expression.

Can anyone clarify where I would put it in this expression ?

Regards,

Neil

[sunny_talwar]

May be try this

=Num(Sum({<YearMonth = {"$(=Date(MonthStart(Today(), -1), 'YYYYMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')

[ngulliver]

Hi, Sunny.

Thanks for your quick response.

I can see how that would work but I am trying to make the calculation react according to dates selected (hence the vMaxYearMonth). So if February 2018 is selected, it will show February 2017.

[sunny_talwar]

Then may be this

=Num(Sum({<YearMonth = {"$(=Date(MonthStart(Max(YearMonth), -1), 'YYYYMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')

[prat1507]

You should use =Date(AddYears(today(),-1))

[prat1507 · Accepted solution]

Hi probably this

=Num(Sum({<YearMonth = {"$(=date(AddYears(Date#(YearMonth ,'YYYYMMM'),-1),'YYYYMMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')