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.
Good Evening All,
Reaching out for help. Having no success solving an issue with calculating previous YTD sales (PYTD). The issue starts with current year sales and trying to match sales for the same period of the previous year. YTD sales works fine but PYTD returns the entire year result which does not work for comparison reasons.
I am using the following formula to create a customer ranking. This formula works fine and provides a list of the TOPN customers for the selected date.:
The desired result is the the PYTD sales for the ranked customers shown for the same time period as YTD sales. So if YTD sales are between 1/01/21 and 3/31/21 the PYTD should be for the same date period. The added catch is to show only PYTD sales for the TOPN customers (Top 5, 10, 25, 50, All as selected in the first formula) for the current period. The result should look like the following for the selection (TopN = 5 (YTD 1/1/21 - 3/31/21)).
Customer YTD Sales PYTD Sales
A $100.00 $98.00
B $50.00 $52.00
C $40.00 $45.00
D $20.00 $20.00
E $10.00 $ 5.00
This problem has been driving me nuts and impacts many of my other YTD calculations in other tabs of the report. Any help would be greatly appreciated.
M_Wex
Hi, @m_wex
Please check the below picture and the sample pbix file's link down below.
I tried to create a sample pbix file based on the scenario, and I hope it suits your case.
All measures are in the sample pbix file.
https://www.dropbox.com/s/0akngbte7h692cc/Mwex.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Good Evening,
Is it possible to send another version of the sample data?
Using version from October 2020.
Thanks,
M_Wex
Hi, @m_wex
Thank you for your feedback.
Sorry, and I am not able to downgrade mine.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Is it possible to post the dax formulas? It might be a while before I get my version upgraded.
Hi, @m_wex
Thank you for your message.
Please check the below.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan Kim,
Sorry for the delayed feedback on this. I had to take a pause for another project.
How do you filter out the blanks on the results without using a filter. The totals are affected when using ISNOTNULL on the YTD Sales, this happens when you look at all sales. You can have a blank in the current year but sales in the previous year which is why filtering out ISNOTNULL does not work.
Good Evening,
Thank you for the post and the sample data set. I am not able to view this yet as the version I am using is not the latest (October 2020) and I need to check with our IT group about updating to the April 2021 version.
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 |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |