Forum Discussion
Rolling 7-Day average calculation.
I am trying to get a rolling 7 day average of a value field. I seem to be having some issues and need some suggestions. Thanks for any input.
Current Output:
Current Measures:
Load to Store Avg =
AVERAGEX('Load to Store','Load to Store'[value])
Rolling Load to Store Avg =
AVERAGEX(
FILTER(ALLSELECTED(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])),
[Load to Store Avg]
)
7 Day Avg =
VAR period = 7
VAR the_first_date =
CALCULATE ( FIRSTDATE ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) )
VAR last_date =
LASTDATE ( Dates[Date] )
VAR sum_in_period =
CALCULATE (
[Rolling Load to Store Avg],
DATESINPERIOD ( Dates[Date], last_date, - period, DAY )
)
RETURN
IF ( last_date - the_first_date >= period - 1, sum_in_period )
7 Day Avg V2 =
CALCULATE (
[Rolling Load to Store Avg] / 7,
DATESINPERIOD (
Dates[Date],
LASTDATE ( Dates[Date] ),
-7,
DAY
),
FILTER (
ALL (Dates[Date] ),
MAX ( Dates[Date] ) < TODAY ()
)
)
Anonymous , Make sure year, month, and day are coming from date table(in visual) and date table is marked as date table
Try with small change
7 Day Avg V3 =CALCULATE (AVERAGEX ( VALUES ( Dates[Date] ), [Rolling Load to Store Avg] ),DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -7, DAY ),Dates[Date] < TODAY ())Rolling Days Formula: https://youtu.be/cJVj5nhkKBw
4 Replies
Anonymous , Try like assume you have measure Rolling Load to Store Avg or the sum measure Rolling Load to Store
Try a new measure like
CALCULATE (
AvergaeX(values(Dates[Date]) , [Rolling Load to Store Avg] ) ,
DATESINPERIOD (
Dates[Date],
LASTDATE ( Dates[Date] ),
-7,
DAY
),
FILTER (Dates, Dates[Date] < TODAY ())
)Thanks for the quick reply. I have created a new measure(7 Day Avg V3) with your dax recommendations. It seems to be mirroring the "Load to Store Avg" calc. Any suggestions?
7 Day Avg V3 =CALCULATE (AVERAGEX ( VALUES ( Dates[Date] ), [Rolling Load to Store Avg] ),DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -7, DAY ),FILTER ( Dates, Dates[Date] < TODAY () ))Rolling Load to Store Avg =AVERAGEX(FILTER(ALLSELECTED(Dates[Date]),Dates[Date] <= MAX(Dates[Date])),[Load to Store Avg])Load to Store Avg =
AVERAGEX('Load to Store','Load to Store'[value])Anonymous , Make sure year, month, and day are coming from date table(in visual) and date table is marked as date table
Try with small change
7 Day Avg V3 =CALCULATE (AVERAGEX ( VALUES ( Dates[Date] ), [Rolling Load to Store Avg] ),DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -7, DAY ),Dates[Date] < TODAY ())Rolling Days Formula: https://youtu.be/cJVj5nhkKBw