Calculating previous daily return (price)


#1

Hi Sam / Hi All,

I need your help / insight. I want to calculate the daily return (price) of financial instruments like currencies, stocks, indices, etc. I connected to a date table & used the DATEADD function to get previous day’s return (price). However, financial instrument don’t trade weekends or at most Sundays! So my previous day’s return show a gaps for days where the instruments show no price (as expected):

How do I show the last given price as the previous price as shown in image? I would like to use this info to calculate the daily price change & subsequently the performance of the instrument through multiple other intermediate steps.

By the way, I tried power query & calculated columns. Both failed performance issues as they could not scale up with huge datasets.

Thanks for assistance


#2

Hi Bare,

Check out the solution detailed here.

This is exactly what you require I believe as DATEADD won’t work for you. This technique will do it.