Calculate daily returns

[Sachy123]

I have a data in the following format

Date	Price
15-12-20	100
14-12-20	50
13-12-20	20
12-12-20	10

I would like to create a measure that can calculate daily returns with following formula

Date	Price	Returns
15-12-20	100	=(100-50)/50
14-12-20	50	=(50-20)/20
13-12-20	20	=(20-10)/10
12-12-20	10	0

[Sachy123]

finally this one works like a charm

new column =
var _max = maxx(filter(table, [Date] < earlier([Date])),[Date])
var _1 = maxx(filter(table, [Date] = _max),[Price])
return
divide([Price] - _1,_1)

[Anonymous]

Hi  @Sachy123  ,

Sorry, this is my negligence. It should be to create a calculated column, not to create a measure. I made a mistake above

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sachy123]

Hi guys, I get a red underline when I use the EARLIER function and there are no autosuggestions. 

[amitchandak · Accepted solution]

@Sachy123 , Try new column like

new column =
var _max = maxx(filter(table, [Date] < earlier([Date])),[Date])
return
[Price] - maxx(filter(table, [Date] = _max),[Price])

or

new column =
var _max = maxx(filter(table, [Date] < earlier([Date])),[Date])
var _1 = maxx(filter(table, [Date] = _max),[Price])
return
divide([Price] - _1,_1)

[Anonymous]

Hi  @Sachy123  ,

Here are the steps you can follow：

1. Create calculated column.

Return =
var _datamax=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<EARLIER('Table'[Date])))
var _price2=CALCULATE(MAX('Table'[Price]),FILTER('Table',[Date]=_datamax))
var _price1=CALCULATE(MAX('Table'[Price]))
var _final_price=(_price1-_price2)/_price2
return IF(_price2=0,BLANK(),_final_price)

2. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.