Forum Discussion
Use a date slicer to dynamically filter last know value for a given listing
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 :
- Add a DAX Column in my HRListing table that gives me for each entry the last starting position date
LastPositionDate =
var currentID = 'HRListing'[EmployeeID]
var result = CALCULATE (
LASTDATE( 'HRListing'[STARTING DATE POSITION]),
ALL(),
'HRListing'[EmployeeID] = currentID
)
return result
- Create a DAX table filtering on that last position date
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
But what I am actually trying to do is to incorporate a date slicerThat 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.
3 Replies
- lbendlin
Super User
But what I am actually trying to do is to incorporate a date slicerThat 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.
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.
