Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
icerdeira
Frequent Visitor

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 ....

 

icerdeira_0-1643371494608.png

 

Thanks!

 

Isaac

1 ACCEPTED SOLUTION

Hi @icerdeira ,

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

I try the following sample data:

vluwangmsft_0-1644286571505.pngvluwangmsft_1-1644286579958.png

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)

vluwangmsft_2-1644286690630.png

 

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

View solution in original post

7 REPLIES 7
v-luwang-msft
Community Support
Community Support

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:

vluwangmsft_0-1643869299506.png

 

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 )

vluwangmsft_1-1643869409786.png

 

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


Best Regards

Lucien

 

 

Hi,

 

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

 

Thanks!

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:

vluwangmsft_0-1643940188488.pngvluwangmsft_1-1643940205595.png

 

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

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.

 

icerdeira_1-1643963123231.png

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

 

This is the table where I have the info

 

icerdeira_2-1643963395500.png

This is the value

icerdeira_3-1643963485233.png

Thanks so much!

 

IC

 

 

 

Hi @icerdeira ,

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

I try the following sample data:

vluwangmsft_0-1644286571505.pngvluwangmsft_1-1644286579958.png

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)

vluwangmsft_2-1644286690630.png

 

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

Hi,

 

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

 

icerdeira_1-1644483482189.pngicerdeira_2-1644483726150.png

 

Thanks!

 

IC

ValtteriN
Super User
Super User

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!







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

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors