topic Re: ROI on Years in DAX Commands and Tips

ROI on Years

icerdeira — Fri, 28 Jan 2022 12:05:30 GMT

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

Re: ROI on Years

ValtteriN — Fri, 28 Jan 2022 12:24:27 GMT

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!

Re: ROI on Years

v-luwang-msft — Thu, 03 Feb 2022 06:25:19 GMT

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

Re: ROI on Years

icerdeira — Thu, 03 Feb 2022 09:09:55 GMT

Hi,

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

Thanks!

Re: ROI on Years

v-luwang-msft — Fri, 04 Feb 2022 02:05:58 GMT

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

Re: ROI on Years

icerdeira — Fri, 04 Feb 2022 08:32:14 GMT

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!

Re: ROI on Years

v-luwang-msft — Tue, 08 Feb 2022 02:19:54 GMT

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

Re: ROI on Years

icerdeira — Thu, 10 Feb 2022 09:02:30 GMT

Hi,

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

Thanks!