Rolling Average Measure Improper Calculation

[Anonymous]

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?

[Anonymous · Accepted solution]

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.