Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Missing dates for 4days Moving Average

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!

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try like

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try like

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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