Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
maurcoll
Helper II
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 NumberRoleFrom DateTo DateMax Date
1Admin Assistant01/02/202231/12/2022N
1Admin Supervisor01/01/202315/08/2023N
1Team Leader16/08/202331/12/2099Y
2Front of House10/03/202431/12/2099Y
3Supervisor01/05/202018/05/2023N
3Regional Lead19/05/202331/12/2099Y
4Assistant28/02/202431/12/2099Y

 

Thank you

1 ACCEPTED SOLUTION
v-yaningy-msft
Community Support
Community Support

Hi, @maurcoll 

You can refer to @Anonymous and @Uzi2019 reply, if it does not work, you can refer to the following DAX.

vyaningymsft_0-1715753002121.png

 

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

View solution in original post

4 REPLIES 4
v-yaningy-msft
Community Support
Community Support

Hi, @maurcoll 

You can refer to @Anonymous and @Uzi2019 reply, if it does not work, you can refer to the following DAX.

vyaningymsft_0-1715753002121.png

 

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

Anonymous
Not applicable

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

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

bchager6_0-1715688594648.png

 

 

Uzi2019
Super User
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
 

Uzi2019_0-1715687480602.png

 

I hope I answered your question!

 

 

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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