Let’s say for instance that you have the following dataset with Amazon closing stock price. It is very simple to calculate daily return if you can sort the rows by dates.

Calculate Daily Stock Return (multiple companies) in Power BI using Dense Ranking

 

But what if you have multiple company’s stock price? And more importantly, how can you calculate daily return for each company in Power BI so you can utilize its dynamic and power BI DAX function?

Calculate Daily Stock Return (multiple companies) in Power BI using Dense Ranking
 

This blog will help you achieve this calculation using DAX and learn a use case of DENSE RANK() function.

Our dataset in this example has adjusted closing price for Amazon, Apple and Google from January 2nd to July 23rd. Keep in mind that our tip will work only if all companies have stock price values for all the dates in the dataset. The challenging part here is to find the previous day’s stock price for each company, on each day.

After importing our dataset, create a new column called “Current Price Row” with the following formula:

 

Current Price Row = RANKX(ALL(‘Stock Price Dataset’), ‘Stock Price Dataset'[Date], ,ASC, Dense)

Calculate Daily Stock Return (multiple companies) in Power BI using Dense Ranking

 

Because the same dates exist for each company DENSE RANK () function will give all the tie values the same ranking.

Next, we will create a new column that will return the ranking of previous day’s stock price. Create a new column called ‘Previous Price Row’ with the following formula

Previous Price Row =

CALCULATE

(

MAX(‘Stock Price Dataset'[Current Price Row]),

ALL(‘Stock Price Dataset’),

‘Stock Price Dataset'[Company ] = EARLIER(‘Stock Price Dataset'[Company ]),

‘Stock Price Dataset'[Date] < EARLIER(‘Stock Price Dataset'[Date]

))

Calculate Daily Stock Return (multiple companies) in Power BI using Dense Ranking

 

This function returns the max ranking number of the row that has the same Company name and with the date earlier than the date of current row. Function EARLIER() serves to refer to current row.

To find stock price create column ‘Previous Day Stock Price’ as follows:

PreviousStockPrice =

CALCULATE

(

MAX(‘Daily Stock Price'[Stock Price]),

ALL(‘Daily Stock Price’),

‘Daily Stock Price'[Company] = EARLIER(‘Daily Stock Price'[Company]),

‘Daily Stock Price'[Day] < EARLIER(‘Daily Stock Price'[Day]),

‘Daily Stock Price'[StockPriceRow] = EARLIER(‘Daily Stock Price'[PreviousStockPriceRow])

)

Calculate Daily Stock Return (multiple companies) in Power BI using Dense Ranking

 

For each row, this function returns the maximum stock price where the company name matches, the date is earlier than current row’s date and the Current Price Row is equal to Previous Price Row. We are using Current Price Row and Previous Price Row ranking number to match the two records.

Last thing we need to do is to create column to calculate daily return based on Adj. Close and Previous Day Stock Price Column.

Daily Return = ‘Stock Price Dataset'[Adj Close]/’Stock Price Dataset'[Previous Day Stock Price] -1

 

Calculate Daily Stock Return (multiple companies) in Power BI using Dense Ranking

 

Let’s give our columns some formatting and create a visualization!

Calculate Daily Stock Return (multiple companies) in Power BI using Dense Ranking

Subscribe to our Newsletter

5600 Tennyson Pkwy
Suite 120
Plano TX 75024.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com