Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
cocohoney
Frequent Visitor

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 :

cocohoney_0-1728009370467.png

 

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

 

 

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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..

View solution in original post

v-yilong-msft
Community Support
Community Support

Hi @cocohoney ,

I create a table as you mentioned and I also create a Date table.

vyilongmsft_0-1728265786741.png

Date = CALENDAR(MIN('Table'[STARTING DATE POSITION]),MAX('Table'[ENDING DATE POSITION]))

vyilongmsft_1-1728265819005.png

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
        )
    )

vyilongmsft_2-1728265937012.png

Next I create a new table.

HRListingLastPosition = 
FILTER(
    'Table',
    'Table'[STARTING DATE POSITION] = 'Table'[LastPositionDate]
)

vyilongmsft_3-1728266047173.png

Finally you will get what you want.

vyilongmsft_4-1728266207437.png

vyilongmsft_5-1728266394708.png

 

 

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.

View solution in original post

3 REPLIES 3
v-yilong-msft
Community Support
Community Support

Hi @cocohoney ,

I create a table as you mentioned and I also create a Date table.

vyilongmsft_0-1728265786741.png

Date = CALENDAR(MIN('Table'[STARTING DATE POSITION]),MAX('Table'[ENDING DATE POSITION]))

vyilongmsft_1-1728265819005.png

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
        )
    )

vyilongmsft_2-1728265937012.png

Next I create a new table.

HRListingLastPosition = 
FILTER(
    'Table',
    'Table'[STARTING DATE POSITION] = 'Table'[LastPositionDate]
)

vyilongmsft_3-1728266047173.png

Finally you will get what you want.

vyilongmsft_4-1728266207437.png

vyilongmsft_5-1728266394708.png

 

 

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.

lbendlin
Super User
Super User

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..

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors