Monthly Rolling Average

[markefrody]

Hi everybody,

I'm creating a monthly rolling average per type using data which has daily records. The DAX I created is not working for monthly rolling average.

Below is what the data looks like:

I created this DAX to get the rolling average wherein it will sum the count of x days and divide it by x days :

Rolling Average Count =

VAR NumofMonths = 12

VAR LastSelectedDate = MAX ('data'[Date MM/DD/YYYY])

VAR Period =

    DATESINPERIOD('data'[Date MM/DD/YYYY], LastSelectedDate, -NumOfMonths, MONTH)

VAR Result =

    CALCULATE (

        AVERAGEX (

            VALUES ('data'[Date MM/DD/YYYY]),

            'data'[Count Sum]

        ),

        Period

    )

RETURN

    Result

For example:

For date 01/03/2023 above, the sum of count is 31. It needs to be added to the previous date wherein 31+144 = 175 . Then divide it by 2 since we are using two consecutive days. (175)/2 = 88

For date 01/04/2023 above, the sum of count is 69. It needs to be added to the previous dates wherein 69+31+144 = 244 . Then divide it by 3 since we are using three consecutive days. (244)/3 = 81

But if I remove the daily date field and use only the monthly field, it is not giving me the correct monthly rolling average below. Will you be able to assist me on the correct DAX to use?

Here is the link containing the sample data for reference.
https://www.dropbox.com/scl/fi/1rfq3smago90ugbzrp5nq/Sample-October-20-2023.pbix?rlkey=wgnp2tqjw98py...

Appreciate your kind help.

Best regards,
Mark V.

[Ritaf1983 · Accepted solution]

Hi @markefrody 
To use these kinds of calculations inefficient way you should add dates table to your model as a first step:

Mark it a dates table:

and then modify your measure to:

rolling average rita =

VAR NumOfMonths = 12

VAR LastCurrentDate =

    MAX ( 'Dates'[Date] )

VAR Period =

    DATESINPERIOD ( 'Dates'[Date], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

    CALCULATE (

        AVERAGEX(

            VALUES ( 'Dates'[Date Year/Month] ),

            [Count Sum]

        ),

        Period

    )

VAR FirstDateInPeriod = MINX ( Period, Dates[Date] )

VAR LastDateWithSales = MAX ( 'data'[Date MM/DD/YYYY])

RETURN

    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

exclude the name Rita 🙂

The link for the modified PBIX 

more information about the importance of dates table :

https://www.youtube.com/watch?v=vTz2Mxt7npY

guide to create it :

https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[markefrody]

@Ritaf1983 
Hi Rita,

Thank you very much for your solution. Confirm it works. Thanks again. Really appreciate it.

Best regards,
Mark V

[Ritaf1983]

You're welcome ☺️