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, 2017/2018, 2018/2019 ....

 

 

Thanks!

 

Isaac

  • 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

7 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,

    You can calculate last year's value by using this logic:

    LY average =

    var _lyear = MAX('Table'[Year])-1 return
    CALCULATE(AVERAGE('Table'[Value]),ALL(Table),Table[Year]=_lyear)

    Now you can just use this to calculate the difference 

    This year = LY average =

    var _year = MAX('Table'[Year]) return
    CALCULATE(AVERAGE('Table'[Value]),ALL(Table),Table[Year]=_lyear)

    ROI = DIVIDE([Last Year]-[This Year],[Last Year])


    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!



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

    Hi icerdeira ，

    If you want to create a new column,use the below dax:

    ROI = 
VAR maxlastyear =
    CALCULATE (
        MAX ( 'Table'[Average Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Year] < EARLIER ( 'Table'[Year] ) )
    )
RETURN
    ROUND ( DIVIDE ( 'Table'[Average Value] - maxlastyear, maxlastyear ), 4 )

    Final get:

     

    And if you want to create a measure:

    ROI2 = 
VAR maxlastyear =
    CALCULATE (
        MAX ( 'Table'[Average Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Year] < MAX ( 'Table'[Year] ) )
    )
RETURN
    ROUND ( DIVIDE ( max('Table'[Average Value]) - maxlastyear, maxlastyear ), 4 )

     

    Did I answer your question? Mark my post as a solution!


    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