Job Analytics - Job On Hire, Job Off Hire, Current On Hire (Historical)

[alex9999]

Hi,

I have recently found a forum post which relates to the problem I am trying to solve:

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

My only issue is that the DAX measures are not working with my dataset. I work in an organisation which holds thousands of rows of data holding details such as "Job On Hire Date" and "Job Off Hire Date". I am trying to make a similar vizualisation to the one at the end of the mentioned forum post, where the "Current Employed Employees" line is replaced with "Current On Hire Jobs", and the "Hired" "Terminated" bars are replaced with "Jobs Hired" "Jobs Offhired".

I can see how the logic works, however I am struggling with the development of my own variation of said vizualisation.

I can work out if a job is on hire by using simple logic (IF Job On Hire Date = NOTBLANK && Job Off Hire Date = BLANK), I just need to incorporate a historical time slicer to find jobs on hire for certain dates, for example: 

There were 874 jobs on hire on 05/01/2022.

For a bit of extra support, here is an example dataset which you could use:

Job ID	Job On Hire Date	Job Off Hire Date
A	11/02/2022	14/02/2022
B	11/02/2022	NULL (Still On Hire)
C	12/02/2022	NULL (Still On Hire)
D	12/02/2022	13/02/2022

[alex9999]

Hi @amitchandak ,

I have just reviewed my initial question and not sure if the dataset is as storytelling as it could be.

I would like to find out how many Jobs were "On Hire" on dates ranging between 2015 and 2022. For example:

There were 800 Jobs on hire in Oct 2020 etc.

Here is an extended dataset if needed:

Job ID	On Hire Date	Off Hire Date
A	12/01/2016	16/02/2018
B	15/04/2017	10/02/2018
C	20/12/2018	NULL
D	30/06/2019	NULL
E	29/07/2019	NULL
F	30/08/2019	NULL

If you were to filter vizualisation to date ranges 20/12/2018 - 14/02/2022 there would be 4 jobs still On Hire.

[amitchandak · Accepted solution]

@alex9999 , Please check the file attached

Small model change, I am keeping both join inactive 

[amitchandak]

@alex9999 , I sharing a file for this blog, please check how date table is used to display current employees.

Can you share the expected results for the data shared by you?