Forum Discussion
Rolling 12 Month Sum strange behavior
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=false&collapse_discussion=true&filter=location&location=forum-board:power-bi-designer&q=rolling%2012%20months&search_type=thread
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!
- Anonymous7 years ago
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...
3 Replies
- parry2k
Super User
Anonymous you can share it using onedrive/google drive
- AnonymousNot applicable
- AnonymousNot applicable
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...