Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Current Employee and Termination Trends

Hi Power BI Community and amitchandak    I've followed this guide - Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend  - for creating employee on hold / off hold trends on a mo...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks for the help. I worked out a solution using a version based on Amits answer.

     

    Current Carers on Hold = CALCULATE(COUNTx(
        FILTER(Carer_On_Hold,Carer_On_Hold[Date_On_Hold]<=max('CalendarHold'[Date]) && (ISBLANK(Carer_On_Hold[Date_Off_Hold])  
            || Carer_On_Hold[Date_Off_Hold]>=max('CalendarHold'[Date]))),
        (Carer_On_Hold[CarerID])),
    CROSSFILTER(Carer_On_Hold[Date_On_Hold],'CalendarHold'[Date],None)) -
    CALCULATE(COUNTx(
        FILTER(Carer_On_Hold,Carer_On_Hold[Date_On_Hold]<=max('CalendarHold'[Date]) && (Carer_On_Hold[Date_Off_Hold] < Carer_On_Hold[vw_Carers.Date_Terminated]) && (ISBLANK(Carer_On_Hold[Date_Off_Hold])
            || Carer_On_Hold[Date_Off_Hold]>=max('CalendarHold'[Date]))),
        (Carer_On_Hold[CarerID])),
    CROSSFILTER(Carer_On_Hold[Date_On_Hold],'CalendarHold'[Date],None))
    I'm sure there is a much more elegant way of doing this but I get the right results!
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , I am assuming Date_On_Hold is like start date of employment. but role of Date_Off_Hold vs Date_Terminate is not clear

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Amit,

 

Apologies for not being clear. So each employee has a Date_Hired and Date_Terminated just as in your blog and when following the blog exactly, this visual is 100% correct.

 

However, in my business, there is a case where Employees are put on hold/suspended. This has a Date_on_Hold and Date_Off_Hold. These can be treated as the hired/terminated dates as in your blog. 

 

My issue is that some records in my business can be as follows:

 

IDDate_HiredDate_TerminatedDate_on_HoldDate_off_HoldStatus
10201/09/201921/03/202004/11/2019(blank)Ex-Carer
29814/01/2020(blank)17/02/202018/05/2020Carer
34512/03/201819/06/202006/04/201901/05/2021Ex-Carer

 

I am trying to plot the Hired/Terminated graph but for on/off hold, however, records such as 102 and 345 are being included through the whole date range when in practice, they should be discounted on all dates after Date_Terminated.

Does that make more sense? Apologies if I'm being unclear.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,

    You can try like this. Get the employee from hire to termination and remove on hold

     

    Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && ((Employee[Status] = "Carer" ) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
    - CALCULATE(COUNTx(FILTER(Employee,Employee[Date_on_Hold]<=max('Date'[Date]) && ((isblank(Employee[Date_off_Hold] && isblank(Employee[Date_Terminated] ))|| coalesce(Employee[Date_off_Hold], Employee[Date_Terminated] )>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

     

    If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

    If not, you might consider providing your dummy pbix that would be helpful for us to investigate it further. 

    You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello,

      Apologies for the late reply, I tried your expression Amit on Friday but I couldn't get it to work. Below is a link to the formula where it shows the error and the model used for this part of my report.

       

      https://imgur.com/a/qlpj5bi

       

      The actual DAX expression that I used with the substituted names is this:

       

      OnHold = CALCULATE(COUNTx(FILTER(vw_Carers,vw_Carers[Date_Approved]<=max('CalendarCarer'[Date]) && ((vw_Carers[Status] = "Carer" ) || vw_Carers[Date_Terminated]>max('CalendarCarer'[Date]))),(vw_Carers[CarerID])),CROSSFILTER(vw_Carers[Date_Approved],'CalendarCarer'[Date],None))
      - CALCULATE(COUNTx(FILTER(Carer_On_Hold,Carer_On_Hold[Date_On_Hold]<=max('CalendarHold'[Date]) && ((isblank(Carer_On_Hold[Date_Off_Hold] && isblank(Carer_On_Hold[vw_Carers.Date_Terminated]))|| coalesce(Carer_On_Hold[Date_Off_Hold], Carer_On_Hold[vw_Carers.Date_Terminated] )>max('CalendarHold'[Date]))),(vw_carers[CarerID])),CROSSFILTER(vw_Carers[Date_Approved],'CalendarCarer'[Date],None))
      )

      I have two calendars using an active date relationship for the 'hired/onhold' and an inactive for the 'terminated/offhold' as per the blog. I feel like this duplication might be causing some issues though. I also had to merge my Carers_on_hold table with my vw_carers(employee information) to get hired/terminated dates in the dax expression.
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thanks for the help. I worked out a solution using a version based on Amits answer.

         

        Current Carers on Hold = CALCULATE(COUNTx(
            FILTER(Carer_On_Hold,Carer_On_Hold[Date_On_Hold]<=max('CalendarHold'[Date]) && (ISBLANK(Carer_On_Hold[Date_Off_Hold])  
                || Carer_On_Hold[Date_Off_Hold]>=max('CalendarHold'[Date]))),
            (Carer_On_Hold[CarerID])),
        CROSSFILTER(Carer_On_Hold[Date_On_Hold],'CalendarHold'[Date],None)) -
        CALCULATE(COUNTx(
            FILTER(Carer_On_Hold,Carer_On_Hold[Date_On_Hold]<=max('CalendarHold'[Date]) && (Carer_On_Hold[Date_Off_Hold] < Carer_On_Hold[vw_Carers.Date_Terminated]) && (ISBLANK(Carer_On_Hold[Date_Off_Hold])
                || Carer_On_Hold[Date_Off_Hold]>=max('CalendarHold'[Date]))),
            (Carer_On_Hold[CarerID])),
        CROSSFILTER(Carer_On_Hold[Date_On_Hold],'CalendarHold'[Date],None))
        I'm sure there is a much more elegant way of doing this but I get the right results!