Rolling 5 Day Average

[jconte12]

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

Here is the DAX:

Rolling Avg =

VAR NumDays = 5 // Days Variable

VAR AvgQuantity =

CALCULATE(SUM('Cons TTL by Day'[QTY_SHIP_ATOMIC]),

FILTER(ALLSELECTED('Cons TTL by Day'),

'Cons TTL by Day'[SELL_DAY_RANK] > MAX('Cons TTL by Day'[SELL_DAY_RANK]) - NumDays &&

'Cons TTL by Day'[SELL_DAY_RANK] <= MAX('Cons TTL by Day'[SELL_DAY_RANK])))

RETURN

AvgQuantity/NumDays

Any ideas?

[v-yangliu-msft · Accepted solution]

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

[davehus]

Hi @jconte12 , Here's another example for moving average if that might help?

5 Day MA = CALCULATE(AVERAGE(SUM(Measure)),

DATESINPERIOD('Cons TTL by Day'[SELL_DAY_RANK] , LASTDATE('Cons TTL by Day'[SELL_DAY_RANK]), -5, DAY))