Forum Discussion
Helper IRolling average with blanks present
Hi,
I am trying to create a 4 week rolling average measure.
Below is what I have so far and it works on my sales data, but for some reason I can't seem to make it work on my budget data (monthly value split out over all weekdays), the budget-table is connected to the master time table on the date-column.
( I made the measure a 10 day average just because it was easier to check the result)
| Date | Budget | Current measure | Sum 10 days BU | Actual 10 day avg | Implied denominator |
| 2025-01-01 | 6 | 0.61 | |||
| 2025-01-02 | 6 | 1.22 | |||
| 2025-01-03 | 6 | 1.84 | |||
| 2025-01-04 | 0 | 1.84 | |||
| 2025-01-05 | 0 | 1.84 | |||
| 2025-01-06 | 6 | 2.45 | |||
| 2025-01-07 | 6 | 3.06 | |||
| 2025-01-08 | 6 | 3.67 | |||
| 2025-01-09 | 6 | 4.29 | |||
| 2025-01-10 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-11 | 0 | 4.29 | 42 | 4.2 | 9.80 |
| 2025-01-12 | 0 | 3.67 | 36 | 3.6 | 9.80 |
| 2025-01-13 | 6 | 3.67 | 36 | 3.6 | 9.80 |
| 2025-01-14 | 6 | 4.29 | 42 | 4.2 | 9.80 |
| 2025-01-15 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-16 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-17 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-18 | 0 | 4.29 | 42 | 4.2 | 9.80 |
| 2025-01-19 | 0 | 3.67 | 36 | 3.6 | 9.80 |
| 2025-01-20 | 6 | 3.67 | 36 | 3.6 | 9.80 |
| 2025-01-21 | 6 | 4.29 | 42 | 4.2 | 9.80 |
| 2025-01-22 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-23 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-24 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-25 | 0 | 4.29 | 42 | 4.2 | 9.80 |
| 2025-01-26 | 0 | 3.67 | 36 | 3.6 | 9.80 |
| 2025-01-27 | 6 | 3.67 | 36 | 3.6 | 9.80 |
| 2025-01-28 | 6 | 4.29 | 42 | 4.2 | 9.80 |
| 2025-01-29 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-30 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-01-31 | 6 | 4.90 | 48 | 4.8 | 9.80 |
| 2025-02-01 | 0 | 4.29 | 42 | 4.2 | 9.80 |
| 2025-02-02 | 0 | 3.67 | 36 | 3.6 | 9.80 |
| 2025-02-03 | 11 | 4.20 | 41 | 4.1 | 9.77 |
| 2025-02-04 | 11 | 5.33 | 52 | 5.2 | 9.75 |
| 2025-02-05 | 11 | 6.47 | 63 | 6.3 | 9.74 |
| 2025-02-06 | 11 | 6.99 | 68 | 6.8 | 9.72 |
| 2025-02-07 | 11 | 7.52 | 73 | 7.3 | 9.71 |
| 2025-02-08 | 0 | 6.90 | 67 | 6.7 | 9.70 |
| 2025-02-09 | 0 | 6.29 | 61 | 6.1 | 9.70 |
| 2025-02-10 | 11 | 6.81 | 66 | 6.6 | 9.68 |
| 2025-02-11 | 11 | 7.95 | 77 | 7.7 | 9.68 |
| 2025-02-12 | 11 | 9.09 | 88 | 8.8 | 9.68 |
| 2025-02-13 | 11 | 9.09 | 88 | 8.8 | 9.68 |
| 2025-02-14 | 11 | 9.09 | 88 | 8.8 | 9.68 |
| 2025-02-15 | 0 | 7.95 | 77 | 7.7 | 9.68 |
| 2025-02-16 | 0 | 6.81 | 66 | 6.6 | 9.68 |
| 2025-02-17 | 11 | 6.81 | 66 | 6.6 | 9.68 |
| 2025-02-18 | 11 | 7.95 | 77 | 7.7 | 9.68 |
| 2025-02-19 | 11 | 9.09 | 88 | 8.8 | 9.68 |
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))
9 Replies
- bhanu_gautam
Super User
Jodallen123 , Try using
4davg_ratioBU =
VAR MaxVisibleDate = MAX('Master Time Table'[Date])
VAR RollingWindow =
FILTER(
'Master Time Table',
'Master Time Table'[Date] <= MaxVisibleDate &&
'Master Time Table'[Date] > MaxVisibleDate - 10
)
RETURN
AVERAGEX(
RollingWindow,
COALESCE([Budget], 0)
)
Jodallen123Thanks for the reply! Unfortunately that does not give me the correct values, I get the result (Forum) below:
sjoerdvnSolution Sage
Hard to say what's going on in your Model. [Current measure Sum 10 days] is also not returning the right values for the first 10 days. I assume [Budget] is a measure. Can you provide that definition?
- sjoerdvn
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))- Jodallen123
Hi, 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.
- sjoerdvn
Yes, that value should be 4.80. In my model it is, using your measure and your dates and budget values; so clearly something else is happening.
So I would check the definition of that [Budget] measure, and the relationship between date table and budget data table.
