Rolling 12 Month Sum strange behavior

[Anonymous]

Hi,

I'm getting strange results with a formula created to calculate a 12 month rolling sum.  The formula seems to work correctly until a filter to the date column is applied.  The data set contains 24 months worth of data.  These are the results I expect.

When a date filter is applied, it changes the values significantly even where I wouldn't expect it to.  For example, I filter out 4/1/2017 and it changes the Defects 12M value for 2/1/2019 (even though that value should be summing the defect_quantity from 3/1/2018 - 2/1/2019.

The formula I'm using for Defects 12M and Receipts 12M is similar to previous blog posts I've found on the subject: https://community.powerbi.com/t5/Desktop/Trailing-12-or-Rolling-12-month-sum/m-p/164419?advanced=fal...

The difference is my data is only reported in months, so I have modified the formula slightly:

Defects 12M = CALCULATE (
SUM(DPM[Defect_Quantity]),
DATESBETWEEN (
'DPM'[Date],
DATEADD(SAMEPERIODLASTYEAR ( LASTDATE ( 'DPM'[Date] )),1,MONTH),
LASTDATE ( 'DPM'[Date] )
)
)

Receipts 12M = CALCULATE (
SUM(DPM[Qty_Received]),
DATESBETWEEN (
'DPM'[Date],
DATEADD(SAMEPERIODLASTYEAR ( LASTDATE ( 'DPM'[Date] )),1,MONTH),
LASTDATE ( 'DPM'[Date] )
)
)

I have a sanitized version of the pbix file I could share, but don't see a way to insert it into this blog post?

Thanks in advance for any help!

[parry2k]

@Anonymous you can share it using onedrive/google drive

[Anonymous]

Does this work?

https://drive.google.com/open?id=1Zga6yc3GaeBl55WqGi_gwxM0jHFJ9p5Z

[Anonymous · Accepted solution]

Ok, well I eventually figured this out.  There were a few report level filters and these were not being applied to the calculated measures posted above.  I just applied the filters in the query editor instead.  It seems strange a report level filter wouldn't also filter a calculated measure...