Moving 3 month total & growth/decline calculation - visual filter resulting in incorrect calculation

[Anonymous]

Hi - I have a set of sales data and am trying to do 2 sequential things:

1 - calculate the moving quarterly total of sales (for any given month sum that months sales + the 2 previous months) which I've successfully done using this DAX:

_mqt_volume = CALCULATE(SUM(SALES[VOLUME]),DATESINPERIOD(DATES[MONTH_START_DATE],LASTDATE(DATES[MONTH_START_DATE]),-3,MONTH))

2 - once the MQT volume has calculated, I then want to calculate the month on month change as a %. Again, I have successfully done this using the following:

_mqt_growth = 
DIVIDE(
    ([_mqt_volume]-calculate([_mqt_volume],DATEADD(DATES[MONTH_START_DATE],-1,MONTH))),
    calculate([_mqt_volume],DATEADD(DATES[MONTH_START_DATE],-1,MONTH))
    )

So the calculations work - however by the very nature of the MQT, I need to filter out the first 3 months of my dataset from any visuals. The problem that I have is that for some reason, when i take out Jan, Feb and Mar 2019 using a visual level filter, all of the _mqt_growth calculations remain accurate apart from Jan, Feb and Mar 2020 - Jan 2020 disappears, and Feb and Mar calculate to something different than when not filtered:

Unfiltered (and correctly calculated values) - ideally want to use a visual level filter to remove the first and last 3 months from the visual. 

This is what happens when i remove Jan, Feb, Mar 2019:

Any ideas how I can resolve this please?

[v-yuaj-msft]

Hi @Anonymous ,

I think maybe it is caused of your data model. 

Could you please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

Best Regards,

Yuna