Missing dates for 4days Moving Average

[Anonymous]

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!

[V-pazhen-msft]

@Anonymous 

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

[amitchandak · Accepted solution]

@Anonymous , Try like

Rolling 4 = CALCULATE(AverageX(values('Date'[Date]),[Revenue]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-4,Day))