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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fanatic
Frequent Visitor

Month End view of Positions filled and unfilled based on job created date and Job Id.

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 IDNo of Candidates neededCandidate IDSTATUSJob Created DateJob Offered Date
1a91a25c01OFFER25/06/202222/09/2022
1a91a25c02OFFER25/06/20223/10/2022
1a91a25c03OFFER25/06/20225/10/2022
1a91a25c04OFFER25/06/20225/10/2022
1a91a25c05OFFER25/06/20225/10/2022
1a91a25c06OFFER25/06/20225/10/2022
1a91a25c07OFFER25/06/202210/11/2022
1a91a25c08OFFER25/06/202210/11/2022
1a91a25c09OFFER25/06/202213/11/2022
1a91a25c10OFFER25/06/202215/11/2022
1a91a25c11OFFER25/06/202216/11/2022
1b51b23s01OFFER5/08/20227/08/2022
1b51b23s02OFFER5/08/20229/08/2022
1b51b23s03OFFER5/08/20221/09/2022
1b51b23s04OFFER5/08/20227/09/2022
1b51b23s05OFFER5/08/20221/10/2022
1c41c25c01OFFER9/12/202210/01/2023
1c41c25c02OFFER9/12/202210/01/2023
1c41c25c03OFFER9/12/202215/01/2023
1c41c25c04OFFER9/12/202217/01/2023

 

Required View/End View:

Job IdJob CreatedMonth/YearNo.of Open PositionsNo.of Positions FilledTime elapsed
1a25/06/2022Jun-22905
1a25/06/2022Jul-229036
1a25/06/2022Aug-229067
1a25/06/2022Sep-228197
1a25/06/2022Oct-2235128
1a25/06/2022Nov-22-25158
1b5/08/2022Aug-223226
1b5/08/2022Sep-221256
1b5/08/2022Oct-220187
1c9/12/2022Dec-224022
1c9/12/2022Jan-230453

 

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

 

View solution in original post

2 REPLIES 2
fanatic
Frequent Visitor

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.

amitchandak
Super User
Super User

@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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors