Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Expert members,
Currently I'm working on a task where I have to create end of month view for number of positions filled and unfilled. Can you give an idea of how to create this view given below.
Main table has all the job related data like job id, no.of candidates need etc.
Main Table:
JOB ID | No of Candidates needed | Candidate ID | STATUS | Job Created Date | Job Offered Date |
1a | 9 | 1a25c01 | OFFER | 25/06/2022 | 22/09/2022 |
1a | 9 | 1a25c02 | OFFER | 25/06/2022 | 3/10/2022 |
1a | 9 | 1a25c03 | OFFER | 25/06/2022 | 5/10/2022 |
1a | 9 | 1a25c04 | OFFER | 25/06/2022 | 5/10/2022 |
1a | 9 | 1a25c05 | OFFER | 25/06/2022 | 5/10/2022 |
1a | 9 | 1a25c06 | OFFER | 25/06/2022 | 5/10/2022 |
1a | 9 | 1a25c07 | OFFER | 25/06/2022 | 10/11/2022 |
1a | 9 | 1a25c08 | OFFER | 25/06/2022 | 10/11/2022 |
1a | 9 | 1a25c09 | OFFER | 25/06/2022 | 13/11/2022 |
1a | 9 | 1a25c10 | OFFER | 25/06/2022 | 15/11/2022 |
1a | 9 | 1a25c11 | OFFER | 25/06/2022 | 16/11/2022 |
1b | 5 | 1b23s01 | OFFER | 5/08/2022 | 7/08/2022 |
1b | 5 | 1b23s02 | OFFER | 5/08/2022 | 9/08/2022 |
1b | 5 | 1b23s03 | OFFER | 5/08/2022 | 1/09/2022 |
1b | 5 | 1b23s04 | OFFER | 5/08/2022 | 7/09/2022 |
1b | 5 | 1b23s05 | OFFER | 5/08/2022 | 1/10/2022 |
1c | 4 | 1c25c01 | OFFER | 9/12/2022 | 10/01/2023 |
1c | 4 | 1c25c02 | OFFER | 9/12/2022 | 10/01/2023 |
1c | 4 | 1c25c03 | OFFER | 9/12/2022 | 15/01/2023 |
1c | 4 | 1c25c04 | OFFER | 9/12/2022 | 17/01/2023 |
Required View/End View:
Job Id | Job Created | Month/Year | No.of Open Positions | No.of Positions Filled | Time elapsed |
1a | 25/06/2022 | Jun-22 | 9 | 0 | 5 |
1a | 25/06/2022 | Jul-22 | 9 | 0 | 36 |
1a | 25/06/2022 | Aug-22 | 9 | 0 | 67 |
1a | 25/06/2022 | Sep-22 | 8 | 1 | 97 |
1a | 25/06/2022 | Oct-22 | 3 | 5 | 128 |
1a | 25/06/2022 | Nov-22 | -2 | 5 | 158 |
1b | 5/08/2022 | Aug-22 | 3 | 2 | 26 |
1b | 5/08/2022 | Sep-22 | 1 | 2 | 56 |
1b | 5/08/2022 | Oct-22 | 0 | 1 | 87 |
1c | 9/12/2022 | Dec-22 | 4 | 0 | 22 |
1c | 9/12/2022 | Jan-23 | 0 | 4 | 53 |
I was able to create a similar table for the months that jobs were offered, but want the table to display even the months where the jobs were not offered and no.of open positions >=0.
Solved! Go to Solution.
@fanatic , Join both dates with a common date table and one join will be inactive activate that using userelationship in a measure and in visual use month, date etc from date tbale
refer example
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
File attached for HR Data
Hi Amit,
Thanks for the quick reply. I went through the blog you shared and was able to get the number of positions filled, but the problem is I'm not able to get it for everymonth since the Job created date, I'm getting only the months from job offered date.
@fanatic , Join both dates with a common date table and one join will be inactive activate that using userelationship in a measure and in visual use month, date etc from date tbale
refer example
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
File attached for HR Data
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |