Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I am currently struggling trying to set up a HR positions listing.
My entry data is the HR listing displaying for each employee, all their positions held and for each position held their starting date (and ending date if applies) :
EmployeeID | LASTNAME | FIRSTNAME | STARTING DATE POSITION | ENDING DATE POSITION | SITE | DPT NAME | POSITION NAME | POSITION LEVEL |
1 | SMITH | Paul | 01/01/2019 | 28/02/2022 | PARIS | CallCenter | Operator | 4 |
1 | SMITH | Paul | 01/03/2022 |
| PARIS | CallCenter | Supervisor | 6 |
2 | ADAM | John | 01/01/2019 | 28/02/2019 | LYON | Telecom Center | Junior Assistant | 3 |
2 | ADAM | John | 01/03/2019 | 28/03/2019 | LYON | Telecom Center | Assistant | 4 |
2 | ADAM | John | 29/03/2019 | 31/03/2019 | LYON | Telecom Center | Senior Assistant | 5 |
2 | ADAM | John | 01/04/2019 | 31/05/2023 | PARIS | Telecom Center | Manager | 7 |
5 | CLARK | Kent | 01/01/2019 | 28/02/2022 | BREST | Financial Direction | Accountant | 6 |
10 | PARKER | Lisa | 01/01/2019 | 30/09/2020 | TOULOUSE | HR Department | Secretary | 5 |
10 | PARKER | Lisa | 01/10/2020 | 28/02/2022 | PARIS | CallCenter | Supervisor | 6 |
10 | PARKER | Lisa | 01/03/2022 | 28/02/2023 | TOULOUSE | HR Department | Manager | 7 |
10 | PARKER | Lisa | 01/03/2023 |
| TOULOUSE | HR Department | Director | 8 |
The end goal is to have the last position held by all employees at a given date.
I managed to do the following :
LastPositionDate =
var currentID = 'HRListing'[EmployeeID]
var result = CALCULATE (
LASTDATE( 'HRListing'[STARTING DATE POSITION]),
ALL(),
'HRListing'[EmployeeID] = currentID
)
return result
HRListingLastPosition = FILTER (
'HRListing',
'HRListing'[STARTING DATE POSITION] = 'HRListing'[LastPositionDate]
)
This is actually working as it is giving my the last uptodate HRListing with the last position held by employees :
EmployeeID | LASTNAME | FIRSTNAME | STARTING DATE POSITION | ENDING DATE POSITION | SITE | DPT NAME | POSITION NAME | POSITION LEVEL |
1 | SMITH | Paul | 01/03/2022 |
| PARIS | CallCenter | Supervisor | 6 |
2 | ADAM | John | 01/04/2019 | 31/05/2023 | PARIS | Telecom Center | Manager | 7 |
5 | CLARK | Kent | 01/01/2019 | 28/02/2022 | BREST | Financial Direction | Accountant | 6 |
10 | PARKER | Lisa | 01/03/2023 |
| TOULOUSE | HR Department | Director | 8 |
But what I am actually trying to do is to incorporate a date slicer in my visual where I can set a date and get the last uptodate HR listing for that current date.
For exemple :
This specific slicer would give me the "screenshot" of the last held position of all employees for this specific date. What I want with such a slicer is this result :
EmployeeID | LASTNAME | FIRSTNAME | STARTING DATE POSITION | ENDING DATE POSITION | SITE | DPT NAME | POSITION NAME | POSITION LEVEL |
1 | SMITH | Paul | 01/01/2019 | 28/02/2022 | PARIS | CallCenter | Operator | 4 |
2 | ADAM | John | 01/04/2019 | 31/05/2023 | PARIS | Telecom Center | Manager | 7 |
5 | CLARK | Kent | 01/01/2019 | 28/02/2022 | BREST | Financial Direction | Accountant | 6 |
10 | PARKER | Lisa | 01/10/2020 | 28/02/2022 | PARIS | CallCenter | Supervisor | 6 |
I tried playing with a slicer with both dates from the HRListing table and the DAX created HRListingLastPosition table but this is not giving me the expected result
Any ideas on how to achieve such a thing ?
Thank you very much
Solved! Go to Solution.
But what I am actually trying to do is to incorporate a date slicer
That slicer must be fed from a disconnected table and then you need to use a measure to read the slicer value and to compute the status for each employee..
Hi @cocohoney ,
I create a table as you mentioned and I also create a Date table.
Date = CALENDAR(MIN('Table'[STARTING DATE POSITION]),MAX('Table'[ENDING DATE POSITION]))
Then I create a calculated column and here is the dax code.
LastPositionDate =
VAR currentID = 'Table'[EmployeeID]
VAR selectedDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
LASTDATE ( 'Table'[STARTING DATE POSITION] ),
FILTER (
ALL ( 'Table' ),
'Table'[EmployeeID] = currentID
&& 'Table'[STARTING DATE POSITION] <= selectedDate
)
)
Next I create a new table.
HRListingLastPosition =
FILTER(
'Table',
'Table'[STARTING DATE POSITION] = 'Table'[LastPositionDate]
)
Finally you will get what you want.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @cocohoney ,
I create a table as you mentioned and I also create a Date table.
Date = CALENDAR(MIN('Table'[STARTING DATE POSITION]),MAX('Table'[ENDING DATE POSITION]))
Then I create a calculated column and here is the dax code.
LastPositionDate =
VAR currentID = 'Table'[EmployeeID]
VAR selectedDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
LASTDATE ( 'Table'[STARTING DATE POSITION] ),
FILTER (
ALL ( 'Table' ),
'Table'[EmployeeID] = currentID
&& 'Table'[STARTING DATE POSITION] <= selectedDate
)
)
Next I create a new table.
HRListingLastPosition =
FILTER(
'Table',
'Table'[STARTING DATE POSITION] = 'Table'[LastPositionDate]
)
Finally you will get what you want.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your help. I had posted my problem twice as my posts were flagged as spams and you gave me a different implementation so thank you for that.
But what I am actually trying to do is to incorporate a date slicer
That slicer must be fed from a disconnected table and then you need to use a measure to read the slicer value and to compute the status for each employee..
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |