Forum Discussion
Issue With Rolling 3 Month Average
I know there are alot of rolling average posts out there, but I haven't been able to find a solution to my issue. I am trying to get a 3 month rolling average of revenue, example below:
For some reason, the calculation I have created for this shows the average revenue at the year level for the last 3 months of that year, example below:
My Net Revenue measure uses USERELATIONSHIP to activate the service date link since I have multiple dates on my fact table:
Total Net Revenue by DOS:=
CALCULATE( [Total Revenue],
USERELATIONSHIP(D_DATE[W_DT_ID],Fact_Charge[W_SERVICE_DT_ID])
)
My attempt at the rolling 3 month average shown here:
Net Revenue R3M:=
VAR NumOfMonths = 3
VAR LastSelectedDate = MAX(D_DATE[Calendar_Date])
VAR Period =
DATESINPERIOD( D_DATE[Calendar_Date], LastSelectedDate, -NumOfMonths, MONTH)
VAR Result =
CALCULATE(
AVERAGEX(
VALUES(D_DATE[Month Year]),
[Total Net Revenue by DOS]
),
Period
)
Return
Result
Any help on this would be fantastic as I have several metrics I need to set up that utilize a 3 month rolling average. Thank you!
22 Replies
daXtreme Thats actually the video that I used to get where I am at now. I copied the DAX from the SQLBI website and changed it to use my specific tables and measure. I just can't figure out why it would only average the last 3 months for each year, but not also work for each individual month like it does in the SQLBI video. I've tried using other measures, removing the other joins to the date table so that there is only one active relationship and nothing seems to be working. Thank you for the response!
Of course it averages the last 3 months when you're on the year level. That's exactly what is expected. How else would you like such a measure to work? If you want it to work differently on each level (year, semester, month, day), then you have to use a different formula (with SWITCH-ing) but that's not what people would expect when they see a 3-month avg.
Hi newguy
Please tryNet Revenue R3M := VAR NumOfMonths = 3 VAR LastSelectedDate = MAX ( D_DATE[Calendar_Date] ) VAR Period = DATESINPERIOD ( D_DATE[Calendar_Date], LastSelectedDate, - NumOfMonths, MONTH ) VAR Result = CALCULATE ( AVERAGEX ( SUMMARIZE ( D_DATE, D_DATE[Month Year], D_DATE[Year] ), [Total Net Revenue by DOS] ), Period ) RETURN ResultI don't think anything wrong with the model. Please try
Net Revenue R3M := AVERAGEX ( SUMMARIZE ( D_DATE, D_DATE[Month Year], D_DATE[Year] ), CALCULATE ( VAR NumOfMonths = 3 VAR LastSelectedDate = MAX ( D_DATE[Calendar_Date] ) VAR Period = DATESINPERIOD ( D_DATE[Calendar_Date], LastSelectedDate, - NumOfMonths, MONTH ) VAR Result = CALCULATE ( AVERAGEX ( VALUES ( D_DATE[Month Year] ), [Total Net Revenue by DOS] ), Period ) RETURN Result ) )
"For some reason, the calculation I have created for this shows the average revenue at the year level for the last 3 months of that year"
And this is correct. That's what it should be.
