Forum Discussion
Issue With Rolling 3 Month Average
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
Result
Here is the Power BI matrix output:
Thank you!