Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
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
Perfect Mr.John, Thanks a lot
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |