Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

 

  • Anonymous's avatar
    Anonymous
    7 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

  • Anonymous you can share it using onedrive/google drive

      • Anonymous's avatar
        Anonymous
        Not 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...