M code to pull the next time a month shows up

[Greyangel]

I would like to build a query in which the results is the last day of june every year. Example since we are in march of 24 right now I would like the table to return 6/30/24, however on 7/1/25 I would like it to return 6/30/26.

[rubinboer · Accepted solution]

hi @Greyangel 

welcome tot he community.

you could do the follwing to determine it:

let
    EndOfJuneDate = (yourEvaluationDate as date) =>
        let
            currentYear = Date.Year(yourEvaluationDate),
            nextYear = currentYear + 1,
            lastDayOfJune = #date(currentYear, 6, 30),
            nextYearFirstDay = #date(nextYear, 1, 1)
        in
            if yourEvaluationDate >= lastDayOfJune then
                nextYearFirstDay
            else
                lastDayOfJune 
in
    EndOfJuneDate

This is what happens

• The if statement checks whether yourEvaluationDate (you can have it check today() as well) is greater than or equal to lastDayOfJune.
• If true (meaning the input date is on or after June 30th), it returns nextYearFirstDay.
• Otherwise (if the input date is before June 30th), it returns lastDayOfJune.