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,
I'm new to Power BI and DAX and I'm sure this has been asked before but I can't find a suitable solution...
I need to report sales quantities for the previous month, same period last year, a delta between them, and then again for YTD (up to last month) and the same for the previous year again with a delta. Below is an example of what i need:
Product | 2019 Month | 2020 Month | Delta | 2019 YTD | 2020 YTD | Delta |
Product A | 150 | 200 | 33% | 300 | 400 | 25% |
Product B | 200 | 150 | -33% | 400 | 300 | -25% |
My dataset contains all of the information in one table (both years).
I have created a calendar table which works fine, as suggested in another post.
Any help is greatly appreciated.
Thanks,
Kristian
Solved! Go to Solution.
For any time intelligence function, you could implement a custom DAX formula.
For any time intelligence function, you could implement a custom DAX formula.
Hello @Kr1s
Assuming you have a data structure like this:
and you have created a calendar table and created a relationship on Date (One-to-Many)
So, you may use measures like this:
Total Orders = SUM(dtTable[Order Quantity])
Previous Month Order = CALCULATE([Total Orders],PREVIOUSMONTH('Calendar'[Date]))
YTD Orders = CALCULATE([Total Orders],DATESYTD('Calendar'[Date))
PY Orders = CALCULATE([Total Orders],SAMEPERIODLASTYEAR('Calendar'[Date]))
Calculating Delta
Current & previous month = [Total Orders] - [Prevoious Month Order]
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedIn
Hi @vivran22
Thanks for the quick reply.I have tried your solution but it isn't quite what I need.
My dataset contains all sales for the year so far but I need it to only display the sales figures for the previous month, i.e. if i run the report today it should show me January's figures. This should only change when the month changes.
The same goes for the YTD calculation - it should only show me data up to the end of January.
Thanks,
Kristian
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |