cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

1 ACCEPTED SOLUTION
Community Support

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!

4 REPLIES 4
Community Support

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!

Super User

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

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

Super User

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

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors