Forum Discussion

markefrody's avatar
markefrody
Post Patron
2 years ago
Solved

Monthly Rolling Average

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=wgnp2tqjw98py98jox732zhaj&dl=0


Appreciate your kind help.

 

Best regards,
Mark V.

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    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 🙂
     
    more information about the importance of dates table :
    guide to create it :
    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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 🙂
     
    more information about the importance of dates table :
    guide to create it :
    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
    • markefrody's avatar
      markefrody
      Post Patron
      2 years ago

      Ritaf1983 
      Hi Rita,

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

      Best regards,
      Mark V