MTD/YTD of Stock Return

[kc_1116]

I am looking to calculate the month-to-date (MTD) and year-to-date (YTD) returns of some stocks.

I have the historical stock price and the corresponding dates, and here are my DAX code for computing the Price of last year end.

Price Last Year = 
var
    dt = CALCULATE(
        MAXX(Price, ENDOFMONTH(DATEADD('Price'[Date], -MONTH('Price'[Date]), MONTH))),
        allexcept('Price', 'Price'[Ticker])
    )
RETURN
    CALCULATE(
        MAX('Price'[Price]),
        'Price'[Date] = dt
    )

It does not work for every stock. For stock like SWX:ROG (Roche), who does not have a price on the 12/31/2024 (In fact, its last trade day was 12/30/2024), my DAX code will fail. I will like my date calculation is wrong and it is not specific to a particular stock. Can you shed me some light how can I correct this please?

Separately, I did the calculation using three measure [Date of Last Year] --> [End Price of Last Year] --> [YTD Return] and they iterate over each stock so the process is slow. Is there more efficient way of doing so? Thanks in advance.

My Power BI File:
https://limewire.com/d/VTO8d#A0AMZXrGgC

[AlexisOlson · Accepted solution]

How about this where you take the last date that does have a price?

Price Last Year = 
VAR _CurrentDate = MAX ( 'Price'[Date] )
VAR _PrevYearEnd = EOMONTH ( _CurrentDate, -MONTH ( _CurrentDate ) )
VAR _LastDate =
    CALCULATE(
        MAX ( 'Price'[Date] ),
        ALL ( 'Price'[Date] ),
        'Price'[Date] <= _PrevYearEnd,
        KEEPFILTERS ( NOT ISBLANK ( 'Price'[Price] ) )
    )
RETURN
    CALCULATE(
        SELECTEDVALUE ( 'Price'[Price] ),
        'Price'[Date] = _LastDate
    )

[burakkaragoz · Accepted solution]

Hi @kc_1116 ,

@AlexisOlson ’s solution is solid and handles the main issue around missing price values on year-end dates. If you’re looking for even more robustness or performance (especially with larger datasets), here are a couple of additional suggestions you could try:

• Instead of recalculating the previous year’s last available price on the fly for every row, consider creating a calculated column or using a summary table that precomputes the year-end (or latest) price for each stock per year. This can sometimes improve performance and make your DAX measures simpler.
• If you often need YTD/MTD returns for multiple stocks or timeframes, building a helper table with [Ticker], [Year], [LastAvailableDate], [LastAvailablePrice] using Power Query or DAX can make your model clearer and easier to audit.
• For more complex scenarios (like fiscal years, non-standard calendars, or irregular trading days), you might want to generalize the logic to dynamically reference the last available trading date before or on the year-end, regardless of weekends or holidays.

If you want an example of building such a summary table or a reusable measure, let me know—I’d be happy to share a step-by-step!

Hope this helps you get even more out of your model!

[AlexisOlson · Accepted solution]

How about this where you take the last date that does have a price?

Price Last Year = 
VAR _CurrentDate = MAX ( 'Price'[Date] )
VAR _PrevYearEnd = EOMONTH ( _CurrentDate, -MONTH ( _CurrentDate ) )
VAR _LastDate =
    CALCULATE(
        MAX ( 'Price'[Date] ),
        ALL ( 'Price'[Date] ),
        'Price'[Date] <= _PrevYearEnd,
        KEEPFILTERS ( NOT ISBLANK ( 'Price'[Price] ) )
    )
RETURN
    CALCULATE(
        SELECTEDVALUE ( 'Price'[Price] ),
        'Price'[Date] = _LastDate
    )