topic Dynamic Percentage Change with Multiple Filters in DAX Commands and Tips

Dynamic Percentage Change with Multiple Filters

Angela30-- — Wed, 02 Nov 2022 04:22:17 GMT

Hi, I'm currently building a metrics dashboard. There is 1 column with the current month metric and 1 column for comparison month metric with a dropdown so it can be changed.

I'm looking to write a measure which dynamically calculates the % change of these two metrics.

I've created a sample table with 1 dim date table (necessary) and 1 fact table which includes the metric value, name of metric and date

The sample data has it at a 1-1 relationship but the real data has 1- many relationship since there are different types of metrics with date table as the dim table

I've created 2 slicers to represent this months metric and last/comparison metric and corresponding metric using min and max. Each separate measure works but the final measure to calcuate the % difference comes up blank

The final calculation includes filter for both dates

Not sure why the individual components work for this calculation but when I bring it together it comes up as blank. Any help would be greatly appreciated!

Re: Dynamic Percentage Change with Multiple Filters

amitchandak — Wed, 02 Nov 2022 04:56:20 GMT

Angela30-- , Change Join from Date table to data as 1-M , date table should be on 1 side

You can use measures for getting MOM

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

If you want two slicer

Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

Re: Dynamic Percentage Change with Multiple Filters

Angela30-- — Wed, 02 Nov 2022 05:14:07 GMT

Hi, thank you for your response.

For stakeholder use, the date is dynamic so I can't put in time dax within the measure, the comparison month could be the previous month or 3 months before that