Forum Discussion
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...
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?
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
2 Replies
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
