Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Rolling 10 Day average

I have a table with the columns [Workbasket_ID], [The_Date], [Received_Inventory]

 

I need to calculate the rolling 10 day average for the received inventory for each workbasket_id. 

 

The DAX below is giving me the same value in all of the workbasket_ids, which is the overall average, I believe.  Please help!

Received_10_Day_Average =
CALCULATE(
AVERAGE(Table[Received_Inventory]),
DATESINPERIOD(
Table[Date],
MAX(Table[Date]),
-10, DAY
),
ALLEXCEPT(Table, Table[Workbasket_ID])
)

"DAX help"
"DAX"

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Consider using the WINDOW function instead which is specifically designed for such scenarios.

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi Anonymous 

     

    Is this what you looking for?

     

    Assuming there is record for every date for every workbasket for minimum 10 days, if not please share business logic in plain english. Calculates last 10 days moving average.

     

    Rolling 10 Day Average =
    VAR _workbasketID = SELECTEDVALUE(Rollingtendayavg[Workbasket_ID])
    VAR _SelDate = SELECTEDVALUE( Rollingtendayavg[Dt])
    VAR _MinDate = CALCULATE( MIN(Rollingtendayavg[Dt]), REMOVEFILTERS(), Rollingtendayavg[Workbasket_ID] = _workbasketID)
    VAR _MaxDate = CALCULATE( MAX(Rollingtendayavg[Dt]), REMOVEFILTERS(), Rollingtendayavg[Workbasket_ID] = _workbasketID)
    VAR _Dt = IF( ISBLANK(_SelDate), _MaxDate, _SelDate)
    VAR _Dtminusten = _Dt - 9
    VAR _Avg =  CALCULATE( AVERAGE(Rollingtendayavg[Rcvd_Inventory]), REMOVEFILTERS(Rollingtendayavg),  Rollingtendayavg[Workbasket_ID] = _workbasketID && Rollingtendayavg[Dt] >= _Dtminusten && Rollingtendayavg[Dt] <= _Dt )

    RETURN IF(_Dtminusten > _MinDate, _Avg, BLANK())
     

     

    Sample Data used

     

    Workbasket_IDDtRcvd_Inventory

    101 January 20242
    102 January 20241
    103 January 20242
    104 January 20243
    105 January 20241
    106 January 20243
    107 January 20242
    108 January 20241
    109 January 20242
    110 January 20244
    111 January 20242
    112 January 20241
    113 January 20242
    114 January 20241
    115 January 20241
    201 January 20242
    202 January 20242
    203 January 20243
    204 January 20241
    205 January 20243
    206 January 20241
    207 January 20242
    208 January 20243
    209 January 20242
    210 January 20241
    211 January 20242
    212 January 20243
    213 January 20242
    214 January 20242