Forum Discussion

jconte12's avatar
jconte12
Frequent Visitor
4 years ago
Solved

Rolling 5 Day Average

Hello, I have an issue with a rolling average I created with DAX in a line chart.  The first 4 days are deflated because it's still thinking I want a 5 day average for the first 1, 2, 3, and 4 days....
Interesting Links
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  jconte12 ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated column.

    rank = RANKX(ALL('Table'),'Table'[date],,ASC)
    mod =
var _mod=MOD('Table'[rank],5)
return
IF(
    _mod=1,1,0)
    Group =
CALCULATE(SUM('Table'[mod]),FILTER(ALL('Table'),'Table'[date]<=EARLIER('Table'[date])))

    2. Create measure.

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

    3. Result:

     

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  jconte12 ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

rank = RANKX(ALL('Table'),'Table'[date],,ASC)
mod =
var _mod=MOD('Table'[rank],5)
return
IF(
    _mod=1,1,0)
Group =
CALCULATE(SUM('Table'[mod]),FILTER(ALL('Table'),'Table'[date]<=EARLIER('Table'[date])))

2. Create measure.

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

3. Result:

 

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