Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Status changes - active/inactive employee record

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

 

StaffNumberEmployeeNameStartDateEndDateEmploymentBasis
032421Joe Bloggs01/01/201901/01/2020Fixed-term contract
032421Joe Bloggs02/01/202005/09/2020Part-time 
032421Joe Bloggs6/09/2020 Full-time
054031Sally Mills10/08/201815/08/2020Part-time
054031Sally Mills16/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

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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
)

     

     

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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.