Year to Date and Previous Year to Date Comparison

[dentonblake]

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

[v-jiascu-msft]

Hi @dentonblake,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

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

[dentonblake]

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

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[dentonblake]

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%

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.