PowerBI - Calculating Daily Percent Returns and Cumulative Returns

Question

I am trying to figure out a way of calculating/visualizing the cumulative percentage change in price (close price) of an ETF in PowerBI, such that I can create a line chart and filter the date range (i.e. select different periods with timeline slicer so I can show cumulative change over 1 month/year-to-date etc).

Effectively, I am trying to calculate the cumulative percent change in column 'close', however I have multiple ETFs (so column 'Symbol' has more than 1 ETF), and this means that there will need to be a different 'minimum close price' per ETF. I would like to be able to do this daily so I can produce a line chart to visualize the data.

I have tried doing this a few ways, am happy to provide details if helpful but me posting this sort of tells you how (un)successful I have been... Quite new to PowerBI/DAX so would really appreciate the help.

Answer 1

Here's one way of adding a column which groups by Symbol, and then adds a column which has the percent change (based on (currentClose-previousClose)/previousClose.

• List.Generate generates the array of percentage change values
• Add that List as a column to the table
• Expand the grouped table

let

//Get table data from someplace
//edit next lines to reflect you actual source
    Source = Excel.Workbook(File.Contents("C:\Users\ron\OneDrive\Documents\Book1.xlsx"), null, true),
    Table21_Table = Source{[Item="Table21",Kind="Table"]}[Data],

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Table21_Table,{
        {"datetime", type date}, {"open", type number}, {"high", type any}, 
        {"low", type any}, {"close", type number}, {"volume", type any}, {"Symbol", type text}}),

//Group rows by Symbol, then add a column with the percent change from close to close
   #"Grouped Rows" = Table.Group(#"Changed Type", {"Symbol"}, {
        {"Percent Change", (t) => Table.FromColumns(
            Table.ToColumns(t) & 
            {List.Generate(
                    ()=>[p=null, idx=0],
                    each [idx]< Table.RowCount(t),
                    each [p= (t[close]{[idx]+1}-t[close]{[idx]})/t[close]{[idx]}, idx=[idx]+1],
                    each [p])},type table [datetime=nullable date, open=nullable number, high=any, low=any, close=nullable number, 
                            volume=any, Symbol=nullable text, percentChange=nullable number])
            }
        
        }),

//expand the created table and set the datatypes
    #"Expanded Percent Change" = Table.ExpandTableColumn(#"Grouped Rows", "Percent Change", {"datetime", "open", "high", "low", "close", "volume", "percentChange"}, {"datetime", "open", "high", "low", "close", "volume", "percentChange"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Percent Change",{{"datetime", type date}, {"open", type number}, {"high", type any}, {"low", type any}, {"close", type number}, {"volume", type any}, {"percentChange", Percentage.Type}})
in
    #"Changed Type1"