Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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:
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.
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
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
Hi,
Thanks so much! It worked, the problem is when I use filters I doesn't work correctly.
Thanks!
IC
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!
Proud to be a Super User!
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |