Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

M code to pull the next time a month shows up

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.

Need Help
  • rubinboer's avatar
    rubinboer
    2 years ago

    hi Anonymous 

     

    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.

1 Reply

  • rubinboer's avatar
    rubinboer
    Icon for Resolver II rankResolver II
    2 years ago

    hi Anonymous 

     

    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.