topic Re: Rolling average with blanks present in DAX Commands and Tips

Rolling average with blanks present

Jodallen123 — Mon, 07 Apr 2025 07:05:23 GMT

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!

Re: Rolling average with blanks present

bhanu_gautam — Mon, 07 Apr 2025 07:18:09 GMT

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

Re: Rolling average with blanks present

sjoerdvn — Mon, 07 Apr 2025 07:46:24 GMT

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?

Re: Rolling average with blanks present

Jodallen123 — Mon, 07 Apr 2025 07:54:31 GMT

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

Re: Rolling average with blanks present

sjoerdvn — Mon, 07 Apr 2025 08:45:29 GMT

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

Re: Rolling average with blanks present

Jodallen123 — Mon, 07 Apr 2025 11:35:33 GMT

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.

Re: Rolling average with blanks present

sjoerdvn — Mon, 07 Apr 2025 13:15:09 GMT

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.

Re: Rolling average with blanks present

Jodallen123 — Mon, 07 Apr 2025 13:23:58 GMT

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

Re: Rolling average with blanks present

sjoerdvn — Mon, 07 Apr 2025 14:04:55 GMT

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?

Re: Rolling average with blanks present

Jodallen123 — Tue, 08 Apr 2025 06:11:52 GMT

I am an idiot, you're absolutely right!