Forum Discussion

nmanselmo's avatar
nmanselmo
Frequent Visitor
8 years ago
Solved

YoY Measure showing only final

Hello All! First post, excited to join community.   I have the following data set and I am trying to add a new measure to the very right hand side calculating the Margin Growth. Any ideas?  ...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this

     

    =IF(ISERROR(CALCULATE([Gross Margin],NEXTYEAR(Calendar[Year]))),([Gross Margin]-CALCULATE([Gross Margin],PREVIOUSYEAR

    (Calendar[Year])))/[Gross Margin],BLANK())

     

    Ensure that the years in your visual are dragged from the calendar table and there is a relationship between the Date column of your source data table to the date column of your calendar table.

     

    Hope this helps.

jmalone's avatar
jmalone
Resolver III
8 years ago

Basic pattern for YoY measure would be:

 

Margin Growth = [Gross Margin] - CALCULATE( [Gross Margin] , DATEADD('Date'[Date], -1, YEAR) )

Where 'Date'[Date] is a column of type = Date with a continuous and unique instance for each day (ie exactly one day for every day of the year). You must have a relationship between this Date table and your FACT table.

 

 

In your example table visual you show both 2016 and 2017. If you use the above measure, you will see Margin Growth for both years. The column within 2017 will show (2017 - 2016), and the 2016 column will show [Gross Margin] for (2016 - 2015).

 

nmanselmo's avatar
nmanselmo
Frequent Visitor
8 years ago
Thanks. I did get this far...in terms of the calculation itself. I was trying to figure out how to do this formula on this type of table shown above while not showing the 2016 value, only the 2017 value. Hope this make sense.