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))
Jodallen123Hi, 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.- 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
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!