topic Re: DAX measure to calculate the percentage change in the stock price in DAX Commands and Tips

DAX measure to calculate the percentage change in the stock price

Snook — Sat, 23 Dec 2023 00:12:12 GMT

Hi all,

I'm trying to calculate the percentage difference for the closing price of a stock compared to the opening on a weekly, monthly and annual basis for each ticker (I presumably need three measures).

The following is an example of the data I'm working with:

Ticker Date Open Close
MMM 01/11/2023 $91.16 $90.56
MMM 02/11/2023 $91.60 $92.31
MMM 03/11/2023 $93.52 $93.86
MMM 06/11/2023 $93.83 $93.52
MMM 07/11/2023 $92.92 $93.15
MMM 08/11/2023 $93.40 $92.32
MMM 09/11/2023 $92.58 $91.70
MMM 10/11/2023 $92.00 $92.81
MMM 13/11/2023 $92.70 $92.69
MMM 14/11/2023 $94.00 $94.97
MMM 15/11/2023 $95.44 $96.82
MMM 16/11/2023 $95.89 $95.71
MMM 17/11/2023 $96.26 $95.34
MMM 20/11/2023 $95.02 $94.98
MMM 21/11/2023 $94.62 $93.99
MMM 22/11/2023 $94.97 $95.37
MMM 24/11/2023 $95.30 $95.95
MMM 27/11/2023 $96.00 $97.07
MMM 28/11/2023 $96.92 $98.51
MMM 29/11/2023 $99.25 $98.47
MMM 30/11/2023 $98.64 $99.07
AOS 01/11/2023 $70.08 $70.81
AOS 02/11/2023 $71.94 $71.35
AOS 03/11/2023 $72.25 $71.56
AOS 06/11/2023 $71.51 $71.09
AOS 07/11/2023 $70.73 $70.65
AOS 08/11/2023 $70.91 $71.14
AOS 09/11/2023 $71.42 $71.37
AOS 10/11/2023 $71.90 $72.82
AOS 13/11/2023 $72.41 $73.38
AOS 14/11/2023 $74.59 $76.02
AOS 15/11/2023 $76.00 $75.00
AOS 16/11/2023 $75.09 $75.64
AOS 17/11/2023 $76.13 $76.38
AOS 20/11/2023 $76.03 $76.11
AOS 21/11/2023 $75.91 $75.87
AOS 22/11/2023 $76.16 $75.88
AOS 24/11/2023 $75.78 $76.01
AOS 27/11/2023 $75.53 $75.96
AOS 28/11/2023 $75.67 $75.12
AOS 29/11/2023 $75.74 $75.10
AOS 30/11/2023 $75.10 $75.36

NB - I've also posted this query on the Excel forum I'm a member of (link).

Thanks in advance,

Snook

Re: DAX measure to calculate the percentage change in the stock price

VahidDM — Sat, 23 Dec 2023 23:44:44 GMT

Hi Snook

To calculate the percentage change in the stock price for each ticker on a weekly, monthly, and annual basis, you can create three separate DAX measures in Power BI. The general approach for each measure is to calculate the opening price at the start of the period and the closing price at the end of the period, and then compute the percentage change between these two values.

Here's how you can create these measures:

1. Weekly Percentage Change

This measure calculates the percentage change from the opening price on the first trading day of the week to the closing price on the last trading day of the week.

Weekly Percentage Change = VAR WeekStart = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Ticker]), STARTOFWEEK('Table'[Date])) VAR WeekEnd = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Ticker]), ENDOFWEEK('Table'[Date])) VAR OpeningPrice = CALCULATE(MIN('Table'[Open]), 'Table'[Date] = WeekStart) VAR ClosingPrice = CALCULATE(MAX('Table'[Close]), 'Table'[Date] = WeekEnd) RETURN IF(OpeningPrice > 0, (ClosingPrice - OpeningPrice) / OpeningPrice, BLANK())2. Monthly Percentage Change

This measure calculates the percentage change from the opening price on the first trading day of the month to the closing price on the last trading day of the month.

Monthly Percentage Change = VAR MonthStart = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Ticker]), STARTOFMONTH('Table'[Date])) VAR MonthEnd = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Ticker]), ENDOFMONTH('Table'[Date])) VAR OpeningPrice = CALCULATE(MIN('Table'[Open]), 'Table'[Date] = MonthStart) VAR ClosingPrice = CALCULATE(MAX('Table'[Close]), 'Table'[Date] = MonthEnd) RETURN IF(OpeningPrice > 0, (ClosingPrice - OpeningPrice) / OpeningPrice, BLANK())3. Annual Percentage Change

This measure calculates the percentage change from the opening price on the first trading day of the year to the closing price on the last trading day of the year.

Annual Percentage Change = VAR YearStart = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Ticker]), STARTOFYEAR('Table'[Date])) VAR YearEnd = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Ticker]), ENDOFYEAR('Table'[Date])) VAR OpeningPrice = CALCULATE(MIN('Table'[Open]), 'Table'[Date] = YearStart) VAR ClosingPrice = CALCULATE(MAX('Table'[Close]), 'Table'[Date] = YearEnd) RETURN IF(OpeningPrice > 0, (ClosingPrice - OpeningPrice) / OpeningPrice, BLANK())

In these measures, replace 'Table' with the actual name of your table. Each measure calculates the start and end of the respective period (week, month, year), finds the opening and closing prices, and then computes the percentage change. The ALLEXCEPT function is used to keep the filter context for the ticker while removing other filters that could affect the calculation of start and end dates.

Ensure that your 'Date' column is properly formatted as a date in Power BI for these calculations to work correctly. Also, note that these calculations assume that your data includes every trading day; if there are missing dates, you might need to adjust the logic to accommodate that.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

LinkedIn | Twitter | Blog | YouTube

Re: DAX measure to calculate the percentage change in the stock price

Snook — Sat, 23 Dec 2023 16:21:01 GMT

Thanks VahidDM

I'm trying to implement your formula but I receive the following error message when I input it.

'Failed to resolve name 'STARTOFWEEK'. It is not a valid table, variable, or function name.'

Any thoughts on where I'm going wrong?

Thanks,

Snook

Re: DAX measure to calculate the percentage change in the stock price

VahidDM — Sat, 23 Dec 2023 23:48:14 GMT

Hey Snook

That was my bad, thanks for letting me know.

try to update those dax with these:

Start of Week: You can calculate the start of the week (assuming Sunday as the first day) with:
StartOfWeek = YourDateColumn - WEEKDAY(YourDateColumn, 1) + 1
This formula adjusts the date to the previous Sunday. If your week starts on a different day, you'll need to modify the formula accordingly.
End of Week: To find the end of the week (assuming Saturday as the last day), you can use:
EndOfWeek = YourDateColumn + (7 - WEEKDAY(YourDateColumn, 1))
This formula adjusts the date to the next Saturday.

Re: DAX measure to calculate the percentage change in the stock price

Snook — Sun, 24 Dec 2023 01:04:57 GMT

I'm still struggling I'm afraid VahidDM 😔 I've tried updating the code a variety of ways but I keep returning an error message. I'd be grateful if you (or someone else) could update the code so I can see where I'm going wrong (I'm a DAX beginner).

I tried adding the formulas above as two new variables but I receive the error message.

'A single value for column 'Date' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

FWIW, this is where I'm at with my updated formula:

Weekly Percentage Change = VAR STARTOFWEEK = 'Table'[Date] - WEEKDAY('Table'[Date], 1) + 1 VAR ENDOFWEEK = 'Table'[Date] + (7 - WEEKDAY('Table'[Date], 1)) VAR WeekStart = CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Ticker]), STARTOFWEEK) VAR WeekEnd = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[Ticker]), ENDOFWEEK) VAR OpeningPrice = CALCULATE(MIN('Table'[Open]), 'Table'[Date] = WeekStart) VAR ClosingPrice = CALCULATE(MAX('Table'[Close]), 'Table'[Date] = WeekEnd) RETURN IF(OpeningPrice > 0, (ClosingPrice - OpeningPrice) / OpeningPrice, BLANK())

Thanks,

Snook