Use a date slicer to dynamically filter last know value for a given listing

[cocohoney]

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

[lbendlin · Accepted 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..

[v-yilong-msft · Accepted solution]

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.

[cocohoney]

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.

[lbendlin · Accepted 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..