rolling average for 3 months showing same value

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

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

 

Hello Anonymous ,

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

Cheers

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.