Status changes - active/inactive employee record

[Anonymous]

Desperately need the amazing support of this group to figure this one out. I am trying to determine the position to date per each employee. Any help would be much appreciated!!

Facts about the data:

• To determine position to date I am forging a variable connection to a date table (UI: date slicer)
• Each employee has one unique identifier - StaffNumber
• Each employee must have one position record and can have several position records
• A position record without an end date means that the person is permanent in that position
• If the last position record for the employee has a position end date = employment ending
• No two dates for a position record per the same employee can overlap, meaning an employee can only have one active record at a time 
• Need to know what the previous position was to the 'current position to date record'

Radacad has a great example below, however, I need to include the position end date?? 

StaffNumber	EmployeeName	StartDate	EndDate	EmploymentBasis
032421	Joe Bloggs	01/01/2019	01/01/2020	Fixed-term contract
032421	Joe Bloggs	02/01/2020	05/09/2020	Part-time
032421	Joe Bloggs	6/09/2020		Full-time
054031	Sally Mills	10/08/2018	15/08/2020	Part-time
054031	Sally Mills	16/08/2020		Full-time

Current role - all in one = 
var _selectedDateRangeEnd=LASTDATE('Date'[Date])
var _selectedDateRangeStart=FIRSTDATE('Date'[Date])
var _EmploymentsBeforeDateRange=FILTER(
    EmploymentHistory,
    EmploymentHistory[Date]<=_selectedDateRangeEnd)
var _LastRoleChangeDate=MAXX(_EmploymentsBeforeDateRange,EmploymentHistory[Date])
var _RoleChangeHistory=CONCATENATEX(_EmploymentsBeforeDateRange,EmploymentHistory[Role],'>',EmploymentHistory[Date],ASC)
var _lastRole=LOOKUPVALUE(EmploymentHistory[Role],EmploymentHistory[Date],_LastRoleChangeDate)
return
if(COUNTROWS(VALUES(EmploymentHistory[Employee]))=1,
'Date range: '&_selectedDateRangeStart&'>>'&_selectedDateRangeEnd&'
Count Rows Employment History: '&COUNTROWS(_EmploymentsBeforeDateRange)&'
Last role date change: '&_LastRoleChangeDate&'
Role history: '&_RoleChangeHistory&'
Last role: '&_lastRole
)

Power BI HR Pattern: DAX Measures for the Last Position to Date: Last Status to Date - RADACAD

[v-henryk-mstf]

Hi @Anonymous ,

Replace the following formula, whether the result returned by return has the wrong sign, etc. 

IF (
    COUNTROWS ( VALUES ( EmploymentHistory[Employee] ) ) = 1,
    'Date range: ' & _selectedDateRangeStart & '>>' & _selectedDateRangeEnd & 'Count Rows Employment History: '
        & COUNTROWS ( _EmploymentsBeforeDateRange ) & 'Last role date change: ' & _LastRoleChangeDate & 'Role history: ' & _RoleChangeHistory & 'Last role: ' & _lastRole
)

Also you can determine if the parameters are correct by returning the individual parameters set by return.

If there is still a problem, can you provide the relevant test data (remove sensitive information) and the desired result, I will answer for you as soon as possible.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I have got the DAX working, however, no values are returning? If anyone can provide any troubleshooting tips that would be so helpful 🙂 

Current role - all in one = 
VAR _selectedDateRangeEnd=LASTDATE('Date'[Date])
VAR _selectedDateRangeStart=FIRSTDATE('Date'[Date])
VAR _EmploymentsBeforeDateRange=FILTER(
    PBIPND_vwPosition_Private,
    PBIPND_vwPosition_Private[EndDate]<=_selectedDateRangeEnd)
VAR _LastRoleChangeDate=MAXX(_EmploymentsBeforeDateRange,PBIPND_vwPosition_Private[EndDate])
VAR _RoleChangeHistory=CONCATENATEX(_EmploymentsBeforeDateRange, PBIPND_vwPosition_Private[PositionCode],">",PBIPND_vwPosition_Private[EndDate],ASC)
VAR _lastRole=LOOKUPVALUE(PBIPND_vwPosition_Private[PositionCode],PBIPND_vwPosition_Private[EndDate],_LastRoleChangeDate)
return
if(COUNTROWS(VALUES(PBIPND_vwPosition_Private[StaffNumber]))=1,
"Date range: "&_selectedDateRangeStart&">>"&_selectedDateRangeEnd&"
Count Rows Employment History: "&COUNTROWS(_EmploymentsBeforeDateRange)&"
Role history: "&_RoleChangeHistory&")
Last role: "&_lastRole
)