Forum Discussion

cocohoney's avatar
cocohoney
Frequent Visitor
2 years ago
Solved

Dynamic HR listing using date slicer

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 ...
DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
2 years ago

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's avatar
    cocohoney
    Frequent Visitor
    2 years ago

    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