DAX Rolling Average Applying at Year Level, But Not Month Level

Viewed 107

I am trying to get a rolling 3/6/12 month average of revenue by month using DAX in a SSAS Tabular model. There is a date table in my model and I have created inactive links between my fact table and my date table for each date in the fact (Date of Service, Invoice Date, etc.). I have created a measure that uses USERELATIONSHIP to activate the specific link I need.

Revenue by DOS:= 
    CALCULATE( [Total Gross Revenue],
    USERELATIONSHIP(D_DATE[W_DT_ID],Fact_Charge[W_SERVICE_DT_ID])
)    

Here is the code I am using to create the rolling average, which I copied from a SQLBI video on the same subject:

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]),
            [Revenue by DOS]
        ),
        Period
    )
Return
    Result    

For some reason when I deploy the model and try to use it in Power BI, it runs this rolling average at the year level, but not the month. For instance, the Power BI matrix visual will show the year 2021 and will have a value that is equal to the average of the last 3 months of 2021, but the individual months show the same value that is contained in the Revenue by DOS measure itself.

Snip From Power BI

The goal is to have each individual month in the Revenue R3M column to be the 3 month average of the Revenue by DOS column.

0 Answers
Related