Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |