Complex open requisitions over time

[tomskelly89]

I have a data set that contains (far more than this):

Requisition ID	Approved Date	Status	Date Filled	Start Date
1	22/10/2021	Filled	02/02/2022	21/06/2022
2	15/11/2021	Filled	25/05/2022	22/06/2022
3	24/06/2022	Filled	29/06/2022	22/11/2022
4	01/01/2022	Sourcing
5	05/06/2022	Interview
6	24/06/2022	Screening

What I need is to be able to show open tickets over time (dating back to 01/01/2020) by month.

Open tickets would be classified as anything without a status of "Filled" and without a fill date populated.

I would then like to present this in a graph that would be split between columns and lines:

Columns - #new starters, #new requisitions
Lines - #open requisitions, #requisitions filled

This graph would need to link to the date so it can be selected to show 2020, 2021, 2022 and so on and will have a Month on the x-axis.

Let me know if that's enough to help with?

[tomskelly89]

Thanks Amit, 

This has certainly solved my issue with the multiple line bar chart I have.

The only thing now is when I want to show the source of hire which is only looking at requisitions that are with a status of "Filled" this is now showing the incorrect %.

This would also need to be filtered by TAq Partner.

Any ideas?

[amitchandak]

@tomskelly89 ,

You need a measure like

Divide( calculate([Measure] , filter(Table, Table[Status] ="Filled")), [Measure])

[tomskelly89]

What would i replace [Measure] with?

[amitchandak]

@tomskelly89 , refer if the blog on similar topic can help

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

[amitchandak · Accepted solution]

@tomskelly89 , refer if the blog on similar topic can help

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