Forum Discussion

Sachy123's avatar
Sachy123
Icon for Helper V rankHelper V
5 years ago
Solved

Calculate daily returns

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 re...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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.