Forum Discussion
Rolling Average Measure Improper Calculation
I have a Rolling 7 day average metric that works well for the most part. However, for some reason, for some entities when I drill down to see the R7, the measure jumps off the page with a very high incorrect calculation:
Rolling 7 Day Average Volume =
CALCULATE (
[Total Volume],
DATESINPERIOD (
'Date'[Date],
LASTDATE ('Date'[Date] ),
-7,
DAY
)
)
/ CALCULATE (
DISTINCTCOUNT ('Date'[Date] ) ,
DATESINPERIOD (
'Date'[Date],
LASTDATE ('Date'[Date] ) ,
-7,
DAY
),NOT(ISBLANK(Volume[Volume])
) )
When I export the data, I have blank volume for that day.
| Date | Concat Date | Total Volume | Rolling 7 Day Average Volume |
| 6/20/2022 0:00 | Monday 06/20 | 3795203 |
Anyone know what I'm missing here?
Hi Anonymous ,
I have created a data sample to try to reproduce your case.
You may try my method to calculate the sum volume of the rolling 7 days:
Measure = CALCULATE(SUM(Volume[Volume]), FILTER('Volume',[Date]>=MAX('Date'[Date])-7 && [Date]<=MAX('Date'[Date])))See the differences:
So Could you please provide me with more details about your table and your problem to help us clarify your scenario? Or share your pbix file after removing sensitive data
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Anonymous ,
I have created a data sample to try to reproduce your case.
You may try my method to calculate the sum volume of the rolling 7 days:
Measure = CALCULATE(SUM(Volume[Volume]), FILTER('Volume',[Date]>=MAX('Date'[Date])-7 && [Date]<=MAX('Date'[Date])))See the differences:
So Could you please provide me with more details about your table and your problem to help us clarify your scenario? Or share your pbix file after removing sensitive data
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.