Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |