Forum Discussion
Quick Measure - 7 day rolling average excluding 0's
Hi All, I've added a quick measure to calculate the 7 day rolling average of some data--- problem is where a record had a value of 0 it is excluded from the average therefore increasing the movin...
Ashish_Mathur that method does not work. Rolling average are still based upon the days with entries when filtered thus inflating the value. For example here is some data from a pbix I am working on.
14 Day Average =
AVERAGEX(DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-14,DAY),[Total Sales])
Date range 8/13 - 8/31
And here are the results :
| Equipment | CostCode | Date | Total Sales |
| BD11007 | 1003-2 | 8/31/2018 0:00 | $310 |
| BD11007 | 1003-2 | 8/25/2018 0:00 | $310 |
| BD11007 | 1003-2 | 8/18/2018 0:00 | $465 |
| Equipment | Sales MTD | 14 Day Average |
| BD11007 | $1,086 | $362 |
The date table in this instance is linked to the sales table as you suggested, but this calculation still filters out days with no sales in the denominator. The average should be $1086 / 14 days = $76
but Dax is reporting $1086 / 3 = $362
I wonder how many Power BI viz out there are incorrect because no one bothered to check the math?
Ashish_Mathur
Super User
Super UserHi,
Share some data and show the expected result.