Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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

  • Habib's avatar
    Habib
    Continued Contributor
    10 years ago

    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's avatar
      Anonymous
      Not applicable
      10 years ago

      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's avatar
        Habib
        Continued Contributor
        10 years ago

        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.