Forum Discussion

icerdeira's avatar
icerdeira
Frequent Visitor
4 years ago
Solved

ROI on Years

Hi!   I need some help on getting the ROI (Return on investment) for Years. The example is Year 2016 = 206.709 € and 2017 = 210.502 the ROI is 1,83%. Is there any way to get the ROI's for 2016/2017...
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi icerdeira ,

    Accoring your infomation ,it main point the  average value is a measure ,not a column.

    I try the following sample data:

    It seems to me that you need to present the averages from the table for the current year, and the averages for [current year - 1 year], and then calculate the roi:

    reaverage = var maxyearlast=CALCULATE(MAX('Table'[year]),FILTER(ALL('Table'),'Table'[year]<MAX('Table'[year])))     return CALCULATE(AVERAGE('Table'[value]),FILTER(ALL('Table'),'Table'[year]=maxyearlast))
    roi = if('Table'[reaverage]=BLANK(),BLANK(),   DIVIDE('Table'[Average value],'Table'[reaverage])-1)

     

    If there are any further questions, you can adjust my template data and present the results you want based on the template data.

     

    Best Regards

    Lucien

icerdeira's avatar
icerdeira
Frequent Visitor
4 years ago

Hi,

 

Thanks for your answers, its work. The problem is the year is variable. Its not always = 4

 

Thanks!

v-luwang-msft's avatar
v-luwang-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi  icerdeira ，

You could also test the below（measure):

roi3 =
VAR maxyear =
    CALCULATE ( MAX ( 'Table'[Year] ), ALLSELECTED ( 'Table' ) )
VAR minyear =
    CALCULATE ( MIN ( 'Table'[Year] ), ALLSELECTED ( 'Table' ) )
VAR maxvalue =
    CALCULATE (
        MAX ( 'Table'[Average Value] ),
        FILTER ( 'Table', 'Table'[Year] = maxyear )
    )
VAR minvalue =
    CALCULATE (
        MAX ( 'Table'[Average Value] ),
        FILTER ( 'Table', 'Table'[Year] = minyear )
    )
RETURN
    ( maxvalue - minvalue ) / minvalue

And   you can select the year you want to compare:

 

And not clear aboutthe year is variable. Its not always = 4. Could you provide a sample data not suit the  previous solution i provided.

 

 

Best Regards

Lucien

  • icerdeira's avatar
    icerdeira
    Frequent Visitor
    4 years ago

    Hi,

     

    Thanks for your help.

     

    The measure is not working. The average is a measure and I think I doesn't work on your measure Roi3.

     

    The average measure is Average value = AVERAGE(Table[value])

     

    This is the table where I have the info

     

    This is the value

    Thanks so much!

     

    IC

     

     

     

    • v-luwang-msft's avatar
      v-luwang-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi icerdeira ,

      Accoring your infomation ,it main point the  average value is a measure ,not a column.

      I try the following sample data:

      It seems to me that you need to present the averages from the table for the current year, and the averages for [current year - 1 year], and then calculate the roi:

      reaverage = var maxyearlast=CALCULATE(MAX('Table'[year]),FILTER(ALL('Table'),'Table'[year]<MAX('Table'[year])))     return CALCULATE(AVERAGE('Table'[value]),FILTER(ALL('Table'),'Table'[year]=maxyearlast))
      roi = if('Table'[reaverage]=BLANK(),BLANK(),   DIVIDE('Table'[Average value],'Table'[reaverage])-1)

       

      If there are any further questions, you can adjust my template data and present the results you want based on the template data.

       

      Best Regards

      Lucien

      • icerdeira's avatar
        icerdeira
        Frequent Visitor
        4 years ago

        Hi,

         

        Thanks so much! It worked, the problem is when I use filters I doesn't work correctly. 

         

         

        Thanks!

         

        IC