Forum Discussion

Guruprasad7's avatar
Guruprasad7
Regular Visitor
4 years ago

calculating rolling average or cumulative average

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

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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])

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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