HR Analytics - Cumulative workload for selection procedures based on start /end date

[Ad_dan29]

Good morning,

I am working on a Talent Acquisition Dashboard and I would like to show the total workload that the team faces monthly in terms of open selection procedures. 

My database looks something like this:

ID | OPENING DATE | HIRING DATE | 
01 |   01/04/2022     |                       |

02 |   03/03/2022     |   08/04/2022  |

03 |    28/01/2022    |   12/05/2022  |

What I would like to achieve is something like a line chart (or an area chart), that shows the cumulative number of ongoing selection procedures at any given month+year.

My doubt is: How can I take into account the closing date (hiring date) when summing up the dates?

For instance, in the example above we would have:

January 2022: 1
February 2022: 1
March 2022: 2
April 2022: 2
May 2022: 1 (because row A is still open)

And then of course plot it. 

I have no clues on how to do it. 

Any suggestions? Thanks!

[amitchandak · Accepted solution]

@Ad_dan29 , Please refer my blog on the same topic

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

[Ad_dan29]

Hi amitchandak

Thank you!

That was ideally exactly what I was looking for. 

I followed step by step your guide, and unfortunately I can't manage to recreate the table and graph in your answer. When I selected the new measure, I see the error message that the data cannot be displayed on the visual.

Any idea on why that is happening?