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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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