Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have two measures - one that calculates YTD sales and the other that calculates Prior YTD sales for a list of +1,000 businesses. I would like to create a third measure that calculates the percentage difference between the two but only when the Prior YTD sales has the same period of sales. For example, if YTD is Jan - Jul 2018 then I would only want to see the %'age difference when I have sales for the Jan - July 2017 period (i.e., apples to apples) - so, if the business opened in June 2017 and only has two months of sales (June & July 2017) then the %'age difference would be null as there are no sales for Jan - May 2017.
Make sense or clear as mud?
Thank you, db
Hi @dentonblake,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @dentonblake,
How to deal with the business with no sales months? In your example, is %'age difference null for the whole year 2018? Or the Jan - May 2018 is null, Jun 2018 - Dec 2018 has values but it starts from Jun 2018? In other words, the YTD of 2018 starts from Jun 2018.
Can you share a dummy sample of your data?
Best Regards,
Dale
Dale-
Thank you for your reply and assistance.
I have currently 7 months of sales for 2018, which are totaled in a YTD 2018 column.
I have 12 months of sales for 2017 and the first 7 months are totaled in a YTD 2017 column.
I have a column that calculates the %'age difference between the two columns.
BUT, if a business was only open for a partial period in the first 7 months of 2017 (e.g., June & July) tOR, if the business closed during the 7 month period - (e.g., in January) then the %'age calculation is skewed.
How do I calculate a %'age difference ONLY IF there is data populated for each month in the two year "YTD" periods; Jan - Jul 2018 and Jan - July 2017?
Please let me know if I can provide any additional information. ~db
Hi,
Share a dataset and show the expected result.
Please see below - does this help? Thank you, ~db
Date | Business 1 | Business 2 | Business 3 | Business 4 | Business 5 |
1/1/2016 | $15,000 | $12,000 | |||
2/1/2016 | $15,000 | $12,000 | |||
3/1/2016 | $15,000 | $12,000 | |||
4/1/2016 | $15,000 | $12,000 | |||
5/1/2016 | $15,000 | $12,000 | |||
6/1/2016 | $15,000 | $12,000 | |||
7/1/2016 | $15,000 | $12,000 | |||
8/1/2016 | $15,000 | $12,000 | |||
9/1/2016 | $15,000 | $12,000 | |||
10/1/2016 | $15,000 | $12,000 | |||
11/1/2016 | $15,000 | $12,000 | |||
12/1/2016 | $15,000 | $12,000 | |||
1/1/2017 | $15,000 | $12,000 | $12,000 | ||
2/1/2017 | $15,000 | $12,000 | $12,000 | ||
3/1/2017 | $15,000 | $12,000 | $12,000 | ||
4/1/2017 | $15,000 | $12,000 | $12,000 | ||
5/1/2017 | $15,000 | $12,000 | $20,000 | $12,000 | |
6/1/2017 | $15,000 | $20,000 | $12,000 | ||
7/1/2017 | $15,000 | $20,000 | $12,000 | ||
8/1/2017 | $15,000 | $20,000 | $12,000 | ||
9/1/2017 | $15,000 | $20,000 | $12,000 | ||
10/1/2017 | $15,000 | $20,000 | $12,000 | ||
11/1/2017 | $15,000 | $35,000 | $12,000 | ||
12/1/2017 | $15,000 | $35,000 | $12,000 | ||
1/1/2018 | $15,000 | $20,000 | $35,000 | $20,000 | |
2/1/2018 | $8,000 | $20,000 | $35,000 | $20,000 | |
3/1/2018 | $8,000 | $20,000 | $35,000 | $20,000 | |
4/1/2018 | $8,000 | $20,000 | $35,000 | $20,000 | |
5/1/2018 | $8,000 | $20,000 | $24,000 | $20,000 | |
6/1/2018 | $8,000 | $20,000 | $24,000 | $20,000 | |
7/1/2018 | $8,000 | $20,000 | $24,000 | $20,000 | |
2017 YTD (Jan 2017 - Jul 2017) | $105,000 | $60,000 | 0 | $60,000 | $84,000 |
2018 YTD (Jan 2018 - Jul 2018) | $63,000 | 0 | $140,000 | $212,000 | $140,000 |
Current YTD % Delta | -40% | -100% | 0% | 253% | 67% |
Expected YTD % Delta | -40% | 0 | 0 | 0 | 67% |
Hi,
You may download my PBI file from here.
Hope this helps.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
88 | |
73 | |
64 | |
60 |