Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lewdow
Helper I
Helper I

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

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. 

 

lewdow_0-1615459272634.png

 

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

 

lewdow_1-1615459329861.png

 

Any ideas how I can resolve this please?

 

1 REPLY 1
v-yuaj-msft
Community Support
Community Support

Hi @lewdow ,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.