In the pivot table above, I created the measures for “Count”, “Previous Month Count”, and “Moving Range” (monthly change in counts), all of which are working as expected. My main dataset is linked to a calendar table (so the Year and Month dimensions are from the calendar table).
Previous Month Count: = CALCULATE((Count),DATEADD('Calendar'(Date),-1,MONTH))
Moving Range: = ABS((Count)-(Previous Month Count))
However, I am having trouble creating an additional measure which calculates the average of the monthly “Moving Range” over a period of X months. I’ve tried several methods, but the closest I got so far is the following:
Moving Range (3M AVG): = DIVIDE(CALCULATE((Moving Range),DATESINPERIOD('Calendar'(Date),MAX('Calendar'(Date)), -3, MONTH)),3)
Moving Range (11M AVG): = DIVIDE(CALCULATE((Moving Range),DATESINPERIOD('Calendar'(Date),MAX('Calendar'(Date)), -11, MONTH)),11)
The results are shown in their respective columns, but for some reason only some values are correct (highlighted in green), while others are way off. For example, the expected “Moving Range (11M AVG)” for December 2019 is ~69.1 (average of the moving ranges from Feb through December), but my measure is generating 15.6.
I really hope someone can point out what I’m doing wrong or show me an alternate path to the expected result. Many thanks in advance!