Forum Discussion
Helper IVMax Date Yes/No
Hi
I need either a measure or a calculated column to identify which is the max date for an employee who has moved roles.
Something similar to below, our system enters a default date for the to date when no date is entered. Ideally i need this to be based on the max from date per employee. So i can create a table which only includes the role based on the max date = Y
| Employee Number | Role | From Date | To Date | Max Date |
| 1 | Admin Assistant | 01/02/2022 | 31/12/2022 | N |
| 1 | Admin Supervisor | 01/01/2023 | 15/08/2023 | N |
| 1 | Team Leader | 16/08/2023 | 31/12/2099 | Y |
| 2 | Front of House | 10/03/2024 | 31/12/2099 | Y |
| 3 | Supervisor | 01/05/2020 | 18/05/2023 | N |
| 3 | Regional Lead | 19/05/2023 | 31/12/2099 | Y |
| 4 | Assistant | 28/02/2024 | 31/12/2099 | Y |
Thank you
Hi, maurcoll
You can refer to Anonymous and Uzi2019 reply, if it does not work, you can refer to the following DAX.LatestFromDate = CALCULATE( MAX('Table'[From Date]), ALLEXCEPT('Table', 'Table'[Employee Number]) ) New to Date = VAR _employeeNumber = SELECTEDVALUE('Table'[Employee Number]) VAR _toDate = CALCULATE ( MAX ( 'Table'[To Date] ), FILTER ( 'Table', NOT ISBLANK ( 'Table'[To Date] ) ) ) VAR _convertToDate = DATEVALUE ( _toDate ) VAR _newToDate = IF ( _toDate = BLANK (), DATE ( 2099, 12, 31 ), _convertToDate ) RETURN _newToDate Max Date = IF(SELECTEDVALUE('Table'[From Date]) = [LatestFromDate] && [New to Date] = DATE(2099,12,31) , "Y" ,"N")Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
4 Replies
maurcollHi
I need it to be based on each employee and the from date as the to date is not always populated so will default to 31/12/2099 for every role that the employee does
maurcoll You can create a new table from within the Modeling tab using the below expression.
FilteredRoles =CALCULATETABLE ( Sheet1, Sheet1[Max Date] = "Y" )
Hi, maurcoll
You can refer to Anonymous and Uzi2019 reply, if it does not work, you can refer to the following DAX.LatestFromDate = CALCULATE( MAX('Table'[From Date]), ALLEXCEPT('Table', 'Table'[Employee Number]) ) New to Date = VAR _employeeNumber = SELECTEDVALUE('Table'[Employee Number]) VAR _toDate = CALCULATE ( MAX ( 'Table'[To Date] ), FILTER ( 'Table', NOT ISBLANK ( 'Table'[To Date] ) ) ) VAR _convertToDate = DATEVALUE ( _toDate ) VAR _newToDate = IF ( _toDate = BLANK (), DATE ( 2099, 12, 31 ), _convertToDate ) RETURN _newToDate Max Date = IF(SELECTEDVALUE('Table'[From Date]) = [LatestFromDate] && [New to Date] = DATE(2099,12,31) , "Y" ,"N")Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
