Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I have a table visual which looks like this.
resourceGroup | Jan | Feb | Mar | Apr | May | Jun | Jul |
RG1 | 1165 | 789 | 63 | 736 | 232 | 115 | 900 |
RG2 | 1389 | 1584 | 721 | 1952 | 1517 | 137 | 114 |
RG3 | 269 | 506 | 1657 | 937 | 199 | 1149 | 1813 |
RG4 | 1377 | 1827 | 360 | 1568 | 652 | 226 | 1618 |
RG5 | 1712 | 158 | 1189 | 503 | 292 | 1128 | 991 |
RG6 | 1299 | 1959 | 808 | 1489 | 1188 | 82 | 675 |
RG7 | 1466 | 1402 | 1596 | 586 | 329 | 93 | 1671 |
RG8 | 550 | 1193 | 18 | 861 | 1387 | 1738 | 1742 |
RG9 | 370 | 1022 | 1548 | 1400 | 532 | 1030 | 64 |
RG10 | 196 | 1045 | 540 | 370 | 1165 | 1761 | 1089 |
What I would like to do is -
Have a slicer for choosing a specific month (this is easily done).
Whenever a specific month is being chosen (from the slicer), the chosen month and the previous month relative to it have to be displayed, along with their costs.
And then display the cost difference between the two via a DAX or whatever you think is better/easier.
Example:
Chosen month in the slicer: July
Output:
resourceGroup | Jun | Jul | Diff |
RG1 | 1206 | 208 | -998 |
RG2 | 874 | 1898 | 1024 |
RG3 | 347 | 847 | 500 |
RG4 | 1245 | 1084 | -161 |
RG5 | 721 | 818 | 97 |
RG6 | 309 | 838 | 529 |
RG7 | 711 | 278 | -433 |
RG8 | 1893 | 632 | -1261 |
RG9 | 1291 | 1377 | 86 |
RG10 | 1736 | 1894 | 158 |
I'm still new to Power BI and any kind of help will be highly appreciated.
Thanks!
Solved! Go to Solution.
@Anonymous , do you have date in you original data. if so you can use time intelligence and date table
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]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])
refer: Power BI — MTD - check without time intelligence option
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
@Anonymous , do you have date in you original data. if so you can use time intelligence and date table
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]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])
refer: Power BI — MTD - check without time intelligence option
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
Thanks, @amitchandak! That worked perfect!
The following measures display same values as output. Does it matter if I use any of them? Or rather, what are the different cases to use them properly?
- last MTD Sales
- last month Sales
- last MTD (complete) Sales
Will find time to study better time intelligence and date table. Really glad there are very active people in this community. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |