calculating rolling average or cumulative average

[Guruprasad7]

Hi All,

Newbie Here.. 
I'm calculating rolling average using below DAX for a seleceted date range, but not finding the desired output.

Even Tried : 
Calculate(Avgerage(sheet1[RATIO]), Filter(ALL('Table'), 'Table'[Date] <= MAX('Table'[Date]) && 'Table'[Date]>= MIN('Table'[Date])))

Here is the sample raw data and desired output.

If i select date range as May 2021 to Oct 2021, line chart should show 89.19 for may month instead of 91%.

Thank you in advance.

Regards,
Guru

[Anonymous]

Hi  @Guruprasad7 ,

Do you mean you want to be unaffected by the filter, you can change the Dax to this format:

Create measure.

Measure =
var _1=MAX('Table'[Date])
return
AVERAGEX(
    FILTER(
        SUMMARIZE(ALL('Table'),
        'Table'[Date],
        "Avg value",AVERAGE('Table'[RATIO])),
        'Table'[Date]<=_1),
        [Avg value])

Best Regards,

Liu Yang

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

[Vera_33]

Hi @Guruprasad7 

You have 2 tables, right? [Ration] is in Sheet1, and date slicer is using Table? What is relationship between these two tables? Table is like a Date table? if yes, 1:N relationship with Sheet1, only use to display May - Oct (sample period), then try it, otherwise provide more info and sample data

test = 
VAR CurMax = MAX(sheet1[Date])
RETURN
AVERAGEX(FILTER(ALL(sheet1),sheet1[Date]<=CurMax),sheet1[Ratio])