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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
smb711
Helper I
Helper I

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

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

Hi @smb711 ,

 

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.

smb711
Helper I
Helper I

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

 

example.PNG

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
)

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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