Forum Discussion
Helper IRolling average with blanks present
Update:
I ran a test with the sample data you provided, and your 10 days rolling average measure actually provides the right values. Or do you want a different beviour, for example exclude non working days? .
Anyway, there is no need to use AVERAGEX here, so for performance I would use code like below.4davg_ratioBU= VAR MaxVisibleDate = MAX('Master Time Table'[Date]) VAR RollingWindow = DATESINPERIOD('Master Time Table'[Date], MaxVisibleDate, -10, DAY) RETURN DIVIDE(CALCULATE([Budget], RollingWindow), COUNTROWS(RollingWindow))
Solution SageUpdate:
I ran a test with the sample data you provided, and your 10 days rolling average measure actually provides the right values. Or do you want a different beviour, for example exclude non working days? .
Anyway, there is no need to use AVERAGEX here, so for performance I would use code like below.
4davg_ratioBU=
VAR MaxVisibleDate = MAX('Master Time Table'[Date])
VAR RollingWindow =
DATESINPERIOD('Master Time Table'[Date], MaxVisibleDate, -10, DAY)
RETURN
DIVIDE(CALCULATE([Budget], RollingWindow), COUNTROWS(RollingWindow))
Helper IHi, that gives me the same value as my current measure, but maybe your version is more efficient. However I don't think it's right? In my dataset above if you look at 2025-01-10, should the value not be 4.8? Not 4.9?
I don't think I need to exclude non-working days.