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 be...
"DAX help"
"DAX"
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