Determine most recent day in list for each month in a year

[arhomberg]

Hello Everyone,

I have a table that has a list of dates, and I would like to create a new column that goes through and finds the most recent date listed within every month for each year.

Here is an example of what I would like to do:

2/10 is the most recent day listed in Feb of 2020 so it receives a 1 and March 31 is the most recent day in March of 2020 so it receives a 1.

Any advice would be greatly appreciated, thanks!

[Anonymous · Accepted solution]

[Is Most Recent...] = // calculated column
var CurrentDate = T[Date to Check]
var MonthStart = EOMONTH( CurrentDate, -1 ) + 1
var NextMonthStart = EOMONTH( CurrentDate, 0 ) + 1
var Result =
    CurrentDate = MAXX(
        filter(
            T,
            MonthStart <= T[Date to Check]
            &&
            T[Date to Check] < NextMonthStart
        ),
        T[Date to Check]
    )
return
    1 * Result