rolling average for 3 months showing same value

[sreddy47652]

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.

[divyed]

Hello @sreddy47652 ,

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

[sreddy47652]

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

[DataNinja777]

Hi @sreddy47652 ,

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,

[johnt75]

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

[sreddy47652]

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

[johnt75]

It works for me, see attached PBIX. I think there must be an issue in the calculation you're using where I put the DISTINCTCOUNT.

[sreddy47652]

@johnt75  it seems better than earlier formula but its not giving accurate results, i need rolling average below like this but based on month number. can you help me how to fix it.

[shafiz_p]

Hi @sreddy47652 
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

[sreddy47652]

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