Plotting incoming and outgoing personnel per position/billet on a timeline/line graphfor HR planning

[isabellamai]

I am using PowerBi desktop with Sharepoint 2019, tracking current active personnel on a sharepoint list with their arrival date and their departure date using a unique identifier (Position/billet identifier).

When people leave the company, I add a new line using the same Position/billet identifier and add their tentative start date/departure date on this same spreadsheet. 

I would like to display this overlap of data for the same position/billet number on a line chart (unless there is a better visual) to help the department managers plan accordingly. 

I foresee using the billet number on the Y-axis and the month/year on theh X-axis --- but can't get it to work. Is it possible to do so - as the intent is to show when those billets might overlap or not overlap for planning purposes.

Fields:

List 1:

Employee Name: text

Department: text

Start Date: Date

End Date: Date

Position/billet identifier: lookup field to below list (List 2)

List 2

Position/billet identifier (Unique Identifier)

Position/billet description name (text)

[pi_eye]

Hi Isabella, do you have some data to further illustrate your example?

Pi

[isabellamai]

Hi Pi - sorry for delay in response.

Here's an example of the main personnel spreadsheet; note, not everyone will have an "End Date" (but we can populate that if needed to be able to display the overlay by position). The challenge I have is - how to have the PowerBi dashboard automatically highlight those positions with overlapping time (whether in month or month/year format) and then to show them either on the same line or directly on top or below each other for those overlapping positions...thus my question on, would I need to add a dependent column/field of some sort where the incoming personnel is dependent on the outgoing personnel. Hope that makes sense.

This is the position lookup list:

Thanks in advance for your time on this.

[pi_eye]

I get it - so in the example above, Myra Stevens and Melanie jones would overlap, as they are within the same job?

[isabellamai]

Exactly - I need to visualize it against a time line so that as that date approaches, leadership will see the ovelap (if any) or the gap (if any) for that position and thus can make alternate plans. Any assistance you can provide would greatly be appreciated.

[isabellamai]

Thanks Pi -t hanks for the quick turnaround.

I like the second visual; however, is it possible to show for example, position 5 with two different bars showing the overlap of time with employee A and employee B? My thoughts were, if the Unique ID was the billet/position identifier, we could map those start/end dates of all list items with that unique identifier? If not, then I guess I need to add a dependency column of sort showing) to have it appear like a Gantt chart? Or do you have any other recommendations? Whatever the solution, it would have to:

- identify the unique identifier billets that have more than one person assigned to it: active, and incoming (yes, we have a status column - active, incoming, and archive)..this will allow us to show when person A plans to leave and when person B will be arriving

- show the overlap in time (by month at a time)

[pi_eye]

Example PBIX here: https://drive.google.com/file/d/15xR8cXoPfOrqRTevRs9VeiJm7V9A8_tc/view?usp=drive_link

[pi_eye]

Hi Isabella

Are you looking for something similar to a gantt chart? This isn't something you can do out of the box in PowerBI, but you can "fake" it by using  a stacked bar chart.

In your example, you can create something similar by using a stacked horizontal bar chart, and adding the start, and duration as "values".

PowerBI doesn't allow (yet) for continous date/time values so the start and duration need to be converted to integers first. Here, I've added these as measures, making the first value "begin" at the earliest date.

Column Begin = (min('Table'[Start]) - [Earliest Start]) *1
Duration = (max('Table'[End]) - min('Table'[Start]))*1

If you add the billet ref# as the dimension in the chart, you will get something that looks like this

The column begin can then be made invisible (or white) to give the impression that the duration is floating. Titles can also be manipulated to give the "look" of  a date.

Custom format for the dates

If you have a much bigger need for the dates to  be displayed, you could do something similar to what is outlined here: https://community.fabric.microsoft.com/t5/Issues/Y-Axis-Custom-Format-String-Issue/idi-p/2255554#M74...

Using this advice, I was able to get some thing that looks like this:

HTH

Pi