Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi probably this
=Num(Sum({<YearMonth = {"$(=date(AddYears(Date#(YearMonth ,'YYYYMMM'),-1),'YYYYMMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')
May be try this
=Num(Sum({<YearMonth = {"$(=Date(MonthStart(Today(), -1), 'YYYYMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')
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.
Then may be this
=Num(Sum({<YearMonth = {"$(=Date(MonthStart(Max(YearMonth), -1), 'YYYYMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')
You should use =Date(AddYears(today(),-1))
Hi probably this
=Num(Sum({<YearMonth = {"$(=date(AddYears(Date#(YearMonth ,'YYYYMMM'),-1),'YYYYMMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')