How to determine which is the current record between multiple records for an employee

[smb711]

I need to show which record is the 'active' record when using a dynamic date slicer

No two records for an employee overlap position starts 

Say I had a date with a date range of April 2012 to May 2013 the current active record would be

592342

Joe Bloggs

1/04/2012

1/05/2013

Contract

Say I had a date slicer with a date range of September 2019 - October 2020 the current active record would be 

592342

Joe Bloggs

16/09/2019

19/10/2020

Part-time

Employee number	Name	Position start date	Position end date	Employment Basis
592342	Joe Bloggs	1/04/2012	1/05/2013	Contract
592342	Joe Bloggs	2/05/2013	3/05/2015	Part-time
592342	Joe Bloggs	4/05/2015	15/09/2019	Full-time
592342	Joe Bloggs	16/09/2019	19/10/2020	Part-time
592342	Joe Bloggs	20/10/2020		Full-time
347977	Sam Bills	19/10/2019	25/12/2020	Part-time
347977	Sam Bills	26/12/2020	15/01/2021	Casual
347977	Sam Bills	16/01/2021	31/12/2021	Full-time

[parry2k]

@smb711 you have to add this a MEASURE not a COLUMN

[smb711]

Thank you for your help with troubleshooting, I don't think it is returning the right result: there should be a sum of hundreds of active FTE 

Data facts to support:

• Every employee only ever gets one employee number
• Each employee can have several position records 
• When the slicer is linked to a date table, which is filtered to cap the distinct count of position records per employee number to show the most recent entry within the date filters 
• SUM the most current entry per an employee per the selected date parameters to determine the actual FTE in past or future months of a year

Filter Employee = 
VAR __startDate = MIN ( DateTable[Date] )
VAR __endDate = MAX ( DateTable[Date] )
RETURN
CALCULATE (
    COUNTROWS ( PBIPND_vwPosition_Private ),

    KEEPFILTERS ( 
        PBIPND_vwPosition_Private[StartDate] >= __startDate
    ),
    KEEPFILTERS (
        PBIPND_vwPosition_Private[EndDate] <= __endDate
   ),
   KEEPFILTERS (
        NOT ISBLANK ( PBIPND_vwPosition_Private[EndDate] ) 
    )

)

[parry2k]

@smb711 try this:

Filter Employee = 
VAR __startDate = MIN ( 'Date'[Date] )
VAR __endDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
    COUNTROWS ( Emp ),

    KEEPFILTERS ( 
        Emp[Position start date] >= __startDate
    ),
    KEEPFILTERS (
        Emp[Position end date] <= __endDate
   ),
   KEEPFILTERS (
        NOT ISBLANK ( Emp[Position end date] ) 
    )

)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smb711]

Thanks this seems to be operating, however, runs into an error:

!A circulate dependency was detected: PBIPND_vwPosition_Private[Column], PBIND_vwPosition_Private[Current FTE], PBIPND_vwPosition_Private[Column].

A circular dependencycy was detected:

• PBIPND_vwPosition_Private[Column],
• PBIPND_vwPosition_Private[Current FTE],
• PBIPND_vwPosition_Private[Column].

A column from the Position vw is getting picked up? Actual FTE is a decimal type column

[parry2k]

@smb711 January 2021 is an old version, you need to get at least Aug/Sep 2021 release and today there is Oct 2021 release.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smb711]

Thanks for letting me know, I don't have access to this version from my workplace. Any suggestions on workarounds? 

[parry2k]

@smb711 Anyhow first and foremost is to solve the issue of why my measure is not working in your case?

 What Power BI version you are using? Can you please confirm?

[smb711]

I have the newest update - version: 2.88.2361.9 64-bit (January 2021)

I think it is the syntax for the variable which is incorrect for my situation and I am not replacing them correctly, could you please replace them using the following field and table names I am using? Apologies

I am not sure what to put in emp as well

Table: PBIPND_vwPositionPrivate

Start date column name: StartDate

End date column name:  EndDate

Employee number column name: StaffNumber

Date table name: Date Table

Date column name: Date

[smb711]

Is it because I am referencing two tables

!! The expression contains multiple columns, but only a single column can be used in a true/false expression that is used as a table filter expression

Filter Employee =

VAR __startDate = MIN ( 'Date Table'[Date] )

VAR __endDate = MAX ( 'Date Table'[Date] )

RETURN

CALCULATE (

    COUNTROWS (PBIPND_vwPosition_Private ),

 

    KEEPFILTERS (

        PBIPND_vwPosition_Private[StartDate] >= __startDate &&

        PBIPND_vwPosition_Private[EndDate] <= __endDate &&

        NOT ISBLANK ( PBIPND_vwPosition_Private[EndDate] )

    )

 

)

[parry2k]

@smb711 assuming you have a date dimension in your model that you will use for the slicer, if not then you can add one using my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

Create a new measure to filter employees that falls in the selected date range:

Filter Employee = 
VAR __startDate = MIN ( 'Date'[Date] )
VAR __endDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
    COUNTROWS ( Emp ),

    KEEPFILTERS ( 
        Emp[Position start date] >= __startDate &&
        Emp[Position end date] <= __endDate && 
        NOT ISBLANK ( Emp[Position end date] ) 
    )

)

Create a table visual, add all the columns you want in the visual, add a visual level filter, and add above measure and where value = 1 and that will do it.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smb711]

Thanks so much Parry2k for your help with this

I am running into a couple of issues

• My employee table with an employee number, position start date and position end date is called Sheet1
• My date table dimension table: [date] date table

Scenario

• Every employee has a position record with a start date
• Some employees may have multiple position records over time 
  • No two-position records per an employee can have an overlapping start date
• I need to identify using a data slicer with month and years (from date table) which is the active position record for the employee at a moment in time so I can only ever see a maximum of one position record per an employee 
• I am not sure how to do this - I am trying to identify one active position record amongst multiple inactive and (1) active postion per an employee 

[smb711]

I guess I am trying to identify the max record at a moment in time whether in the past or present.