Rolling average with blanks present

[Jodallen123]

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)

4davg_ratioBU =

VAR MaxVisibleDate = MAX('Master Time Table'[Date])

VAR RollingWindow =

    DATESINPERIOD('Master Time Table'[Date], MaxVisibleDate, -10, DAY)

RETURN

    AVERAGEX(

        RollingWindow,

        COALESCE([Budget], 0)

    )

The data looks like this:

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

Any ideas why my measure give me incorrect values? Does it have to do with the 0:es on weekends? I tried the measure with no 0es on the weekends but it made no difference. 

Any help is much appreciated!  

[sjoerdvn · Accepted solution]

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]

My guess is that the daily budget is not 6.00 for January, but more to the tune of 6.13, that would explain the numbers. Please check the formatting you are using. Or are you (re)calculating the daily budget in that measure?

[Jodallen123]

I am an idiot, you're absolutely right! 

[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.

[Jodallen123]

Okay! 

The measure looks like this and and is connected to the master time table on a date column (one to many from the master time table), it should be a really straight forward model, or at least so I thought... Any ideas what could be going wrong with this measure? 

#NetSalesPVBU = CALCULATE( SUM( SalesBudget[DailyValue]), SalesBudget[SubCategory] = "Budget" )

[sjoerdvn]

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?

[bhanu_gautam]

@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)
)

[Jodallen123]

Thanks for the reply! Unfortunately that does not give me the correct values, I get the result (Forum) below: