cancel
Showing results for
Did you mean:
Regular Visitor

## year over year variable

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

7 REPLIES 7
Established Member

## Re: year over year variable

Hi @benkilloy 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
Regular Visitor

## Re: year over year variable

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?

Established Member

## Re: year over year variable

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.

Regular Visitor

## Re: year over year variable

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
Established Member

Regular Visitor

## Re: year over year variable

It doesn't error out it just shows up blank

Moderator

## Re: year over year variable

Hi @benkilloy,

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.

Best Regards,

Qiuyun Yu

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

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 265 members 3,225 guests
Recent signins: