How to get the max day within month list

[Moha_shanti]

Hi,

I have a tbale contains list of materials with Reservations approval dates, as shown below, I want to get MAX day in month for each material

the column MAX Day in Month In Material is the results required

Reservation	Material	Reservation Approval date	MAX Day in Month In Material
6070121	130005014246	19-May-23	19-May-23
6368193	130005014246	16-May-23	19-May-23
6367930	130005014246	16-May-23	19-May-23
6368116	130005014246	17-May-23	19-May-23
6363382	130005014246	18-May-23	19-May-23
6265553	130005014246	30-Apr-23	30-Apr-23
6218614	130005014246	28-Apr-23	30-Apr-23
6219954	130005014246	26-Apr-23	30-Apr-23
6206696	130005014246	25-Apr-23	30-Apr-23
6186092	130005014246	19-Apr-23	30-Apr-23
6179992	130005014246	18-Apr-23	30-Apr-23
5542775	130005014246	03-Jan-23	03-Jan-23
5552568	130005014246	03-Jan-23	03-Jan-23
5551822	130005014246	03-Jan-23	03-Jan-23
5541394	130005014246	02-Jan-23	03-Jan-23
5543304	130005014246	02-Jan-23	03-Jan-23
5543226	130005014246	02-Jan-23	03-Jan-23
5543082	130005014246	02-Jan-23	03-Jan-23
6070121	130005010010	19-May-23	19-May-23
6368193	130005010010	16-May-23	19-May-23
6363382	130005010010	18-May-23	19-May-23
6265553	130005010010	30-Apr-23	30-Apr-23
6218614	130005010010	28-Apr-23	30-Apr-23
6219954	130005010010	26-Apr-23	30-Apr-23
6206696	130005010010	25-Apr-23	30-Apr-23
5542775	130005010010	03-Jan-23	03-Jan-23

[johnt75 · Accepted solution]

You could create a column like

MAX Day in Month In Material =
VAR MonthStart =
    EOMONTH ( 'Table'[Reservation approval date], -1 ) + 1
VAR MonthEnd =
    EOMONTH ( 'Table'[Reservation approval date], 0 )
VAR MaxDate =
    CALCULATE (
        MAX ( 'Table'[Reservation approval date] ),
        ALLEXCEPT ( 'Table', 'Table'[Material] ),
        DATESBETWEEN ( 'Table'[Reservation approval date], MonthStart, MonthEnd )
    )
RETURN
    MaxDate

[Moha_shanti]

Perfect Mr.John, Thanks a lot