Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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...
@Anonymous you can share it using onedrive/google drive
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |