Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have the set analysis
Sum({<Year=,Month=,DateKey={">=$(=Num(MonthStart(Max(DateKey))))<=$(=Max(DateKey))"}>}[Sales Amount])
to calculate my Current Year month to date sales but want to now calculate my Prior Year MTD Sales and am having difficulty.
Same with QTD.
Thank you
Hi,
Try below method in set expression for Prior MTD
=SUM({<Year={'$(vPriorYear)'},Month={'$(vMaxMonth)'},Day={'<=$(vMaxDay)'},Date=,MonthYear=>}Amount)
vPriorYear - =vMaxYear -1
vMaxMonth =month(max(Date))
vMaxDay =day(max(Date))
MTD:
Sum(if(InMonthToDate(DateKey,today(),0),[Sales Amount]))
Prior Year MTD:
Sum(if(InMonthToDate(DateKey,today(),-12),[Sales Amount]))
QTD:
Sum(if(InQuarterToDate(DateKey,today(),0),[Sales Amount]))
Prior Year QTD:
Sum(if(InQuarterToDate(DateKey,today(),-4),[Sales Amount]))