Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dentonblake
Helper II
Helper II

Year to Date and Previous Year to Date Comparison

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

6 REPLIES 6
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @dentonblake,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Please see below - does this help?  Thank you, ~db

 

DateBusiness 1Business 2Business 3Business 4Business 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,0000$60,000$84,000
2018 YTD (Jan 2018 - Jul 2018)$63,0000$140,000$212,000$140,000
Current YTD % Delta-40%-100%0%253%67%
Expected YTD % Delta-40%00067%

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.