Dynamic HR listing using date slicer

[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 :

a "01.01.2022" slicer would give me the "snapshot" 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!

[lbendlin · Accepted solution]

Your slicer needs to be fed by a disconnected Dates table, and then you need to use measures to read the slicer value and calculare data visibility accordingly.

[cocohoney]

Thank you so much DataNinja for the extended help provided and taking the time to implement my usecase in pbi.

It is very much appreciated

[DataNinja777 · Accepted solution]

Hi @cocohoney ,

Building on lbendlin's solution to use a disconnected calendar table, let me provide additional guidance on how to implement it. To achieve your required snapshot at any point in time, your data model would look as follows:

Next, you can write a DAX measure like the one below:

Position held = 
SUMX (
EmployeeFact,
    IF (
EmployeeFact[STARTING DATE POSITION] <= max('Calendar'[Date])
&& EmployeeFact[ENDING DATE POSITION] >= max('Calendar'[Date]),
        1,
        BLANK ()
    )
)

The output looks like this, and you can use the yyyy-mm slicer to get the snapshot for your selected date.

I have attached an example pbix file for your reference.

Best regards,

[cocohoney]

Thank you so much DataNinja for the extended help provided and taking the time to implement my usecase in pbi.

It is very much appreciated

[lbendlin · Accepted solution]

Your slicer needs to be fed by a disconnected Dates table, and then you need to use measures to read the slicer value and calculare data visibility accordingly.

[cocohoney]

Thank you so much. This is really appreciated.