topic Re: Job Analytics - Job On Hire, Job Off Hire, Current On Hire (Historical) in DAX Commands and Tips

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

alex9999 — Mon, 14 Feb 2022 13:07:31 GMT

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-trend/ba-p/882970

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

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

amitchandak — Mon, 14 Feb 2022 13:54:53 GMT

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?

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

alex9999 — Mon, 14 Feb 2022 14:11:15 GMT

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.

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

amitchandak — Mon, 14 Feb 2022 14:45:22 GMT

alex9999 , Please check the file attached

Small model change, I am keeping both join inactive