Max Date Yes/No

[maurcoll]

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

[v-yaningy-msft · Accepted solution]

Hi, @maurcoll 

You can refer to @bchager6 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 Team

If 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

[bchager6]

@maurcoll  You can create a new table from within the Modeling tab using the below expression.

FilteredRoles =

 CALCULATETABLE ( Sheet1, Sheet1[Max Date] = "Y" )

[Uzi2019]

Hi @maurcoll 

Just  create simple calculated column and use below dax

Yes_No =

var M= MAX(Emp[To Date])

var F= IF(Emp[To Date]= M,"Y","N")

RETURN F

I hope I answered your question!

[maurcoll]

Hi

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