Forum Discussion
Issue With Rolling 3 Month Average
daXtreme So I guess my question is how do I get the measure to take the 3 month rolling average for each month like he does in the SQLBI link you sent? When I try to use the same measure the values at the month level are same in both the Net Revenue and the Rolling 3 Month Revenue columns - only the yearly amounts have changed. The end result I'm looking for is exactly what the end result in the SQLBI video is, mine just isn't coming out the same way.
Solution SageIf it isn't, this means your model is different to the model Alberto uses. Can you please post a readable picture of your model?
I pared it down to just the basics and still got the same issue with the following:
The only link being the D_DATE[W_DT_ID] --> FACT_CHARGE[W_SERVICE_DT_ID]
The measures I have are:
*Total Net Revenue:= SUM(Fact_Charge[GROSS_CHARGES_AMOUNT])
*Total Net Revenue by DOS:=
CALCULATE( [Total Net Revenue],
USERELATIONSHIP(D_DATE[W_DT_ID],Fact_Charge[W_SERVICE_DT_ID])
)*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[Year_Month]),
[Total Net Revenue]
),
Period
)
Return
Result*Net Revenue R3M v2:=
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[Year_Month]),
[Total Net Revenue by DOS]
),
Period
)
Return
ResultHere is the Power BI matrix output:
Thank you!
Looks like it was just me not realizing the date table had to be specifically marked in SSIS for these calculations to work. Thank you again for all the help!