Forum Discussion

czuniga's avatar
czuniga
Helper III
6 years ago

Trailing 18 Month Sum

Hello, I have a measure that works perfectly for other similar calculations, but for some reason isn't working as hoped with "Sales". Oddly enough, it works fine for trailing 12 months, but becomes inaccurate at 18 months. The DAX is:

 

18 Mo. Sales =
CALCULATE(
sum('Daily Sales'[Sales]),
FILTER(
ALL(Rolling_Calendar_Lookup),
AND(
Rolling_Calendar_Lookup[Date] <= MAX(Rolling_Calendar_Lookup[Date]),
DATEADD(
Rolling_Calendar_Lookup[Date],18,MONTH)> MAX(Rolling_Calendar_Lookup[Date]
))))

 

Any help would be greatly appreciated. Thank you!

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    czuniga , I doubt it should be -18, if we want to go in the past. I Typically uses

    Rolling 18 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-18,MONTH))
    Rolling 18 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-18,MONTH))