Representing Personnel Position Statuses across Months

[Warren_FDA]

I have two years' experience with Power BI and got overconfident.  I've run into a problem where I can't figure out how to graphically represent the status of personnel positions within my agency.  Here is a photo of both the simplified data and what I would want:

Three positions involving two employees over eight months. The real data has 131 positions over 12 years.

In the top portion, you can see Veronica Jones transfers from one position to another.  Ed Norton is in the other position, then goes on a detail, then leaves.  If a row has no end date, that means that is its current status.  This affects three Position ID over the course of eight months.  The bottom is the graph I would like to make in Power BI.

I am having trouble grappling with the approach:

1. After creating the above, I decided it should be a heat map, but would a bar graph be somehow easier?
2. I created a date table, but because only one relationship can be primary at a time, how do  get it to look at both Start Date and End Date to see if it falls within one of the ranges, then show the correct Position Status?
3. I've thought about creating a calculated measure that determines if a date falls between the Start and End Date, but to get to this step I still have to solve bullet 2.

I think I have been overthinking this to the point I'm not sure how to approach it anymore.  I've made large models before and this small one has me stumbling.  Thank you.

[v-tangjie-msft · Accepted solution]

Hi @Warren_FDA ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to enter power query, add custom column -> expand the column -> add custom date column.

= Table.AddColumn(#"Changed Type", "Custom", each if [End date]=null then null else List.Dates([Start Date],Duration.Days([End date]-[Start Date])+1,#duration(1,0,0,0)))

= Table.AddColumn(#"Expanded Custom", "Date", each if [Custom] = null then [Start Date] else [Custom])

(3) We can create a measure.

color = SWITCH(SELECTEDVALUE('Table'[Position Status]),
"Filled","green",
"Detail out","blue",
"Vacant","grey")

Set the conditional format.

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Warren_FDA]

Hi Neeko-  Thanks for tackling this!  I followed your directions and was able to replicate what you did (I just noticed you added a .pbix file, sorry).  It works as needed, but when I add labels to the cells, I notice this happening:

Note how for DP4 and DP28, the month of December says Filled, but they aren't green. Same with DP22, months June and September lack a label color.

I understand what is happening.  If you were to add Days to the columns the status and color match perfectly.  But when you roll up to the month level, those months where there are status changes the label doesn't have the associated color.  I modified the calculated measure to the following:

color =
SWITCH(
    CALCULATE(MIN('sample data'[Position Status])),
    "Filled", "green",
    "Detail out", "blue",
    "Vacant", "grey"
)

This resulted in the following:

The code in the "color" balanced measure has to use MIN([Position Status]), that way it will match up with the FIRST ([Position Status]) in the visual.

I'll run forward with this solution, thank you very much!