Calculating previous daily return (price)


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


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.