Max 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 d...

Need Help

Anonymous

2 years ago

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 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

demo05151.pbix38 KB

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")

Forum Discussion

Max Date Yes/No