Hi I am trying to find the moving average for a data sets with missing data but I am having hard time getting an accurate averages.
I did create a date table on the power query side and merge it with my data but for some odd reasons the average is not including the missing dates.
This is what Power Bi is showing:
But when I did it in Excel, I got the correct moving 4 days average:
Please any assist or help is greatly appreciated.Thank You!
Go to Solution.
@learn2fly , Try like
Rolling 4 = CALCULATE(AverageX(values('Date'[Date]),[Revenue]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-4,Day))
View solution in original post
@learn2fly It work if you revenue is a column in the same table. I guess the problem is your [revenue] is a measure calculated based on the relationship between the tables. If you can just share the sample pbix, we should able to detect the issue.
Paul Zheng _ Community Support Team
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.