Issue With Rolling 3 Month Average

[newguy]

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!

[daXtreme]

"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.

[tamerj1]

Hi @newguy 
Please try

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 (
            SUMMARIZE ( D_DATE, D_DATE[Month Year], D_DATE[Year] ),
            [Total Net Revenue by DOS]
        ),
        Period
    )
RETURN
    Result

[newguy]

@tamerj1 I tried using the SUMMARIZE function but ended up with the same result. I'm wondering if it an issue with the underlying data model or something. Is there potentially a different way to get the same rolling average by month result?

[tamerj1]

@newguy 

I 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
    )
)

[newguy]

@tamerj1 Same issue unfortunately. Let me make sure I am using the correct date fields in your example. Here are some of my date table fields and an example value from the table:

*Calendar_Date = 1/1/2022 12:00:00 AM

*Calendar_Month = 1

*Calendar_Year = 2022

*Month_Year = January 2022

*Year_Month = 2022-01

I tried switching the SUMMARIZE function to be

SUMMARIZE(D_DATE,D_DATE[Calendar_Month], D_DATE[Calendar_Year])

as well and didnt have any luck.

[tamerj1]

@newguy 

What are you expecting to see at the year level?

[newguy]

@tamerj1 I really don't need it at the year level, only the month. Each month should show the average of the Net Revenue of the previous three months. So May would show the average of March/April/May.

Ideally I would have a card showing the 3 month average for the most recent month, or a line/bar combo graph showing the Total Revenue using the bars, and then the line would be the rolling average (something like the image below).

This specific calculation would also be used in another measure where I would take total payments in a given month, then divide it by the 3 month average net revenue to get a collection rate. Thanks!

[tamerj1]

@newguy 

So your measure is calculating the correct values. Then where is the problem?

[newguy]

@tamerj1 My apologies, the excel screenshot was just an image I found on google of an example of what I am trying to do - not my actual data/measures. In my Power BI file I am still having the previous issue. Here is a screenshot of where I am currently at, where Net Revenue R3M is the first attempt you sent me to fix the problem, and Net Revenue R3Mv2 is the second attempt.

[tamerj1]

@newguy 

Alright. Let's try it this way

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 =
    AVERAGEX (
        SUMMARIZE (
            CALCULATETABLE ( D_DATE, Period ),
            D_DATE[Month Year],
            D_DATE[Year]
        ),
        [Total Net Revenue by DOS]
    )
RETURN
    Result

[newguy]

I gave this a shot this morning and still had the same issue. I created a new, pared down version with just the fact and date tables (posted in reply to another comment), no dimensions. Seem to be getting the same type of issue even with just two tables and one join. 

[tamerj1 · Accepted solution]

Hi @newguy 
Would you please double check and confirm if the date table is marked as date table. In all cases would you please add REMOVEFILTERS ( D_Date ) inside CALCULATE and let me know if you get any different results. 

[newguy]

It looks like that was the issue. I didn't realize the date table had to be specifically marked inside my SSIS Tabular model for those types of calculations to work. I apologize for all the time spent on something so simple - part of being new to DAX/Power BI I guess -  but I appreciate all of the help! Thank you so much!

[tamerj1]

@newguy 

No problem. Actually it was late at night yesterday when I was trying to find a solution and directly after that went to sleep. The first thing that came to my mind before I close my eyes was the date table.  Actually time intelligence functions include many hidden functions that do not work if the table was not marked as date table. DATESINPERIOD includes REMOVEFILTERS which was apparently not working. 

[daXtreme]

Computing rolling average in DAX - SQLBI

[newguy]

@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!

[daXtreme]

If you want this to only work on a full month level, then you'll have to detect that you are exactly on this level, and then apply the formula. So, first you have to see if in the current context only one year-month is visible and if it has exactly as many days as the year-month when there are no filters. If this is true, then apply the formula. This way you'll get rid of the average from the year level and any other level than year-month.

[daXtreme]

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.

[newguy]

@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.

[daXtreme]

If it isn't, this means your model is different to the model Alberto uses. Can you please post a  readable picture of your model?