year over year variable

[Anonymous]

Trying to create an easy year over year growth chart but not finding any really simply charts

Would love to show it on a water fall chart

Also not easy to find calculation for year over year variance 

[Habib]

Hi @Anonymous Waterfall will be a good choice.

YOY should be simple if you want to use Calendar date 🙂

1. Calculate Last Year Values using SAMEPERIODLASTYEAR
2. Calcuate Variance based on TY-LY
3. Calcuate Varinace % based on Variance/LY

[Anonymous]

Do you create custom column or calculated table?

If if want to show year over year change for past three years, how would this approach change?

[Habib]

It should be column. Please refer to below example.

I took sales sample for few years.

DateKey	Sales
7/1/2006	100
7/1/2007	290
7/1/2008	300
7/1/2009	200
7/1/2010	130
7/1/2011	230
7/1/2012	950
7/1/2013	250
7/1/2014	340
7/1/2015	455

Created "LY Sales" column using below formula

LY Sales = CALCULATE(SUM(Sales[Sales]),ALL(Sales),PREVIOUSYEAR(Sales[DateKey]))

Added new column for LY Variance % as below

LY Variance % = CALCULATE(DIVIDE((SUM(Sales[Sales])-SUM(Sales[LY Sales])),SUM(Sales[LY Sales])), ALL(Sales[DateKey]))

Then put the DateKey and LY Variance % on waterfall chart. Here is the result.

[Anonymous]

Thank you for your help on this, I am almost there I think

Here is my formula 

PrioryearSales = CALCULATE(SUM('Sales Data Structure'[AMOUNT]),ALL('Sales Data Structure'[AMOUNT]),PREVIOUSYEAR('Sales Data Structure'[Shipping Date]))

The table looks like this, it is a list of individual invoices that I am summing up using the Amount column 

DOCUMENT NO.

Z-NUMBER

SHIPPING DATE

AMOUNT

SHIP TO STATE

Rep

6618	296	10/8/2013	$                     -	OK	RL
16458-1	410	10/8/2013	$     35,464.00	WI
16529-2	410	10/8/2013	$     30,628.00	WI
16626	210	10/9/2013	$        1,980.00	FL	RL
16607	220	10/9/2013	$        2,240.00	WI	JTS
16547	220	10/9/2013	$           160.00	WI	JTS
16596	220	10/9/2013	$           101.00	WI	JTS
16558	229	10/9/2013	$           810.00	IN
16610	231	10/9/2013	$        3,570.00	WI	JTS
16609	231	10/9/2013	$        2,460.00	WI	JTS

[Habib]

In your provided information, your formula should work.

[Anonymous]

It doesn't error out it just shows up blank 

[v-qiuyu-msft]

Hi @Anonymous,

Would you please provide more information about "it just shows up blank"?

To calculate difference among each year. You can create a calculated table group by the year and create a calculated column to return the previous year amount for each row, then create another calculated column to return difference. I have created a sample .PBIX file for your reference, you can take a look it to check if it can meet your requirements.

If you have any question, please feel free to ask.

Best Regards,

Qiuyun Yu