cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
benkilloy Regular Visitor
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
Habib Established Member
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 Smiley Happy

 

  1. Calculate Last Year Values using SAMEPERIODLASTYEAR
  2. Calcuate Variance based on TY-LY
  3. Calcuate Varinace % based on Variance/LY
benkilloy Regular Visitor
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?

Habib Established Member
Established Member

Re: year over year variable

It should be column. Please refer to below example.

 

I took sales sample for few years.

 

DateKeySales
7/1/2006100
7/1/2007290
7/1/2008300
7/1/2009200
7/1/2010130
7/1/2011230
7/1/2012950
7/1/2013250
7/1/2014340
7/1/2015455

 

 

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.

 

YOY.png

 

 

 

 

benkilloy Regular Visitor
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-NUMBERSHIPPING DATE AMOUNT SHIP TO STATERep
661829610/8/2013 $                     -  OKRL
16458-141010/8/2013 $     35,464.00WI 
16529-241010/8/2013 $     30,628.00WI 
1662621010/9/2013 $        1,980.00FLRL
1660722010/9/2013 $        2,240.00WIJTS
1654722010/9/2013 $           160.00WIJTS
1659622010/9/2013 $           101.00WIJTS
1655822910/9/2013 $           810.00IN 
1661023110/9/2013 $        3,570.00WIJTS
1660923110/9/2013 $        2,460.00WIJTS
Habib Established Member
Established Member

Re: year over year variable

In your provided information, your formula should work.

benkilloy Regular Visitor
Regular Visitor

Re: year over year variable

It doesn't error out it just shows up blank 

Moderator v-qiuyu-msft
Moderator

Re: year over year variable

Hi @benkilloy,

 

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

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.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 265 members 3,225 guests
Please welcome our newest community members: