Forum Discussion

renanpinheiro's avatar
renanpinheiro
Advocate II
9 years ago
Solved

calculation relating to previous years

I have a table with multiple values per company and each line represents a year. I need to calculate a one-year value (one line) divided by another year of value (other line). But these lines has no...
  • jessicammoss's avatar
    jessicammoss
    9 years ago

    Assuming a DataTable with columns Company, Year, and Value, and you want to get a YoY value, you can use this calculation:

     

    YoY = [Value] / CALCULATE(MAX([Value]), FILTER('DataTable', EARLIER('DataTable'[Company]) = 'DataTable'[Company] && EARLIER('DataTable'[Year]) = 'DataTable'[Year] + 1))

     

    This results in this output with sample data:

     

    HTH,

    Jessica

jessicammoss's avatar
jessicammoss
Advocate I
9 years ago

Assuming a DataTable with columns Company, Year, and Value, and you want to get a YoY value, you can use this calculation:

 

YoY = [Value] / CALCULATE(MAX([Value]), FILTER('DataTable', EARLIER('DataTable'[Company]) = 'DataTable'[Company] && EARLIER('DataTable'[Year]) = 'DataTable'[Year] + 1))

 

This results in this output with sample data:

 

HTH,

Jessica

renanpinheiro's avatar
renanpinheiro
Advocate II
9 years ago

Hi Jessica,
worked perfectly.
Pen include DIVIDE function, not to be written in the infinity values:

 

YoY = DIVIDE([Value]; CALCULATE(MAX([Value]), FILTER('DataTable', EARLIER('DataTable'[Company]) = 'DataTable'[Company] && EARLIER('DataTable'[Year]) = 'DataTable'[Year] + 1)); BLANK())


thank you so much.