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
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
Solved! Go to Solution.
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
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
@maurcoll You can create a new table from within the Modeling tab using the below expression.
Hi @maurcoll
Just create simple calculated column and use below dax
I hope I answered your question!
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
96 | |
87 | |
70 |
User | Count |
---|---|
166 | |
133 | |
129 | |
102 | |
98 |