Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

rolling average for 3 months showing same value

Hi team,

i am trying to create the rolling average for 3 months but it was giving same values as base value, can you help me to fix for rolling average for 3 months.

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • johnt75's avatar
    johnt75
    1 year ago

    You can try

    Rolling Average =
VAR DatesToUse =
    WINDOW (
        -2,
        REL,
        0,
        REL,
        ALLSELECTED ( 'Date'[Year], 'Date'[Month number] ),
        ORDERBY ( 'Date'[Year], ASC, 'Date'[Month number], ASC )
    )
VAR Result =
    AVERAGEX ( DatesToUse, CALCULATE ( DISTINCTCOUNT ( 'Table'[User ID] ) ) )
RETURN
    Result
  • divyed's avatar
    divyed
    1 year ago

    Hello Anonymous ,

     

    You can use below dax for your purpose , please replace table names and fileds:

     

    rolling_3_month_Avg =
    AVERAGEX(
        WINDOW(
            -2,REL,0,REL,
            ALLSELECTED(Test_Avg[Month_number],Test_Avg[Year],Test_Avg[Users]),
            ORDERBY(Test_Avg[Month_number]),
            PARTITIONBY(Test_Avg[Year])
        ),
        CALCULATE(AVERAGE(Test_Avg[Users])
    )
    )
     

     

    Did I answer your query ? Mark this as solution if this helps, appreciate your Kudos.

     

    Cheers

     

  • divyed's avatar
    divyed
    1 year ago

    Hello Anonymous ,

     

    It is giving as expected , here is the illustration, please suggest if you have different logic

     

     

    Cheers

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    Please try the following dax.

    Rolling Average (3 Months) = 
VAR difference = DATEDIFF(MINX(ALL('Table'),'Table'[Date]),MAX('Table'[Date]),MONTH) --The difference in months from the min date.
VAR _value = CALCULATE(DISTINCTCOUNT('Table'[Users]) ,DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-3,MONTH)) --Total value of 3 months.
RETURN
SWITCH(
    TRUE(),
    difference=0,_value,
    difference=1,_value/2,
    difference>1,_value/3
  )

     

    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

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

14 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi Anonymous 
    It is better to provide representative date to get the exact solution.


    However, you could try this:

    RollingAvg3Months = 
CALCULATE(
    AVERAGEX(
        DATESINPERIOD(
            'DateTable'[Date],
            LASTDATE('DateTable'[Date]),
            -3,
            MONTH
        ),
        AVERAGE('YourTable'[Value])
    )
)

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      i tried already above formula still returns same values for base value

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    You can try

    Rolling Average =
VAR DatesToUse =
    WINDOW (
        -2,
        REL,
        0,
        REL,
        ALLSELECTED ( 'Date'[Year], 'Date'[Month number] ),
        ORDERBY ( 'Date'[Year], ASC, 'Date'[Month number], ASC )
    )
VAR Result =
    AVERAGEX ( DatesToUse, CALCULATE ( DISTINCTCOUNT ( 'Table'[User ID] ) ) )
RETURN
    Result
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      i tried above forumala but it always return as 1 only instead of rolling average

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi Anonymous ,

     

    Here is a measure for calculating a 3-month rolling average at the month level:

    Rolling Average (3 Months) = 
VAR CurrentMonth = MAX('Calendar'[Date])
RETURN 
    AVERAGEX(
        DATESINPERIOD('Calendar'[Date], CurrentMonth, -3, MONTH),
        CALCULATE(SUM('FactTable'[Value]))
    )

     

     

     

    This measure calculates the rolling average by using the DATESINPERIOD function to define a 3-month window and then applies AVERAGEX to compute the average for the specified period. The calendar table ensures the calculation respects the relationship with the fact table.

     

    Best regards,

  • divyed's avatar
    divyed
    Super User
    1 year ago

    Hello Anonymous ,

     

    You can use below dax for your purpose , please replace table names and fileds:

     

    rolling_3_month_Avg =
    AVERAGEX(
        WINDOW(
            -2,REL,0,REL,
            ALLSELECTED(Test_Avg[Month_number],Test_Avg[Year],Test_Avg[Users]),
            ORDERBY(Test_Avg[Month_number]),
            PARTITIONBY(Test_Avg[Year])
        ),
        CALCULATE(AVERAGE(Test_Avg[Users])
    )
    )
     

     

    Did I answer your query ? Mark this as solution if this helps, appreciate your Kudos.

     

    Cheers

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      divyed it's working some what better than earlier formulas but need rolling average like this below image, can you help me to fix this.

       

      • divyed's avatar
        divyed
        Super User
        1 year ago

        Hello Anonymous ,

         

        It is giving as expected , here is the illustration, please suggest if you have different logic

         

         

        Cheers

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

    Please try the following dax.

    Rolling Average (3 Months) = 
VAR difference = DATEDIFF(MINX(ALL('Table'),'Table'[Date]),MAX('Table'[Date]),MONTH) --The difference in months from the min date.
VAR _value = CALCULATE(DISTINCTCOUNT('Table'[Users]) ,DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-3,MONTH)) --Total value of 3 months.
RETURN
SWITCH(
    TRUE(),
    difference=0,_value,
    difference=1,_value/2,
    difference>1,_value/3
  )

     

    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

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