Calculating the Weight of Gap Openings in a Stock Using DAX (Power BI)

~ K.A.

This post follows up on Calculating the Weight of Gap Openings in a Stock Using SQL. The subject is the same, but the approach is via DAX, a language used in Microsoft products such as Power BI, Excel and Azure Analysis Services.

The data is preloaded into a table named MSFT in Power BI. The stock data includes dividend adjustments.

The DAX code below calculates the required variables incrementally, much like the CTE approach in SQL.

DAX
EVALUATE
VAR add_calculated_columns_1 = // Create the first virtual table and add the close_lagged_1 column. The column close_lagged_1 must be calculated first in order to be used in the subsequent virtual table.
    ADDCOLUMNS(
        'MSFT',
        "close_lagged_1",
        VAR CurrentDate = 'MSFT'[date]
        RETURN
            CALCULATE(
                MAX('MSFT'[close]),
                FILTER(
                    'MSFT',
                    'MSFT'[date] = 
                    CALCULATE(
                        MAX('MSFT'[date]),
                        FILTER(
                            'MSFT',
                            'MSFT'[date] < CurrentDate
                        )
                    )
                )
            )
    )
//RETURN add_calculated_columns_1
VAR add_calculated_columns_2 = // Create the second virtual table and add the calculated column “opening_gap.”
	ADDCOLUMNS(
		add_calculated_columns_1,
		"opening_gap",
		IF(ISBLANK([close_lagged_1]), BLANK(), 'MSFT'[open] - [close_lagged_1]),
		"intraday_diff",
		'MSFT'[close] - 'MSFT'[open]
	)
//RETURN add_calculated_columns_2
VAR summary_table =  // Create the third virtual table to calculate summary statistics: “opening_period,” “closing_period,” “sum_gap_openings,” and “sum_intraday_diff.”
    SUMMARIZE(
        add_calculated_columns_2,
        "opening_period", CALCULATE(FIRSTNONBLANK('MSFT'[open], 1), FILTER('MSFT', 'MSFT'[date] = MIN('MSFT'[date]))),
        "closing_period", CALCULATE(LASTNONBLANK('MSFT'[close], 1), FILTER('MSFT', 'MSFT'[date] = MAX('MSFT'[date]))),
	      "sum_gap_openings", SUMX(add_calculated_columns_2, [opening_gap]),
        "sum_intraday_diff", SUMX(add_calculated_columns_2, [intraday_diff])
	)
//RETURN summary_table
RETURN // Return a table with the final calculations: “pct_change_period,” “pct_gap_openings,” and “pct_intraday_diff.”
ADDCOLUMNS(
    summary_table,
    "pct_change_period", ROUND([closing_period] / [opening_period] - 1, 2),
    "pct_gap_openings", ROUND([sum_gap_openings] / ([closing_period] - [opening_period]), 2),
    "pct_intraday_diff", ROUND([sum_intraday_diff] / ([closing_period] - [opening_period]), 2)
)

Conclusion

In 2023, MSFT increased by 56%. Within the total increase in 2023, the weight of gap openings was 54%, and the weight of intraday changes was 46%.

As an extreme example, if one buys at the opening price at the beginning of each trading day and sells at the closing price, 54% of the total rise during the year would be missed compared to buying at the beginning of 2023 and selling at the end. In other words, 54% of the annual increase would be missed; this means missing out on a (56% x 54% = ) 30% profit relative to the index value at the beginning of the year. This shows that gap openings can have a significant impact in the long term.

Leave a Reply