100% Stacked Column Chart that displays "On Time"/"Late" for each step of a 5-step process

[brdmcdgll]

Disclaimer: I am totally new to Power BI and this forum and I have wracked my brain all week about this, so please be easy on me 🙂

I am currently working on a few year-end reports for my organization, and I have been asked to display a 100% stacked column visualization measuring whether or not KPI's were met for each step in a 5-step process. We have multiple projects that go through this 5-step process.

Data about each project is collected by staff in my organization and entered into columns titled "Step 1 Due Date" and "Step 1 End Date" and so on for each of the 5 steps (see below for example of data). Each row represents a project. I have set up calculated columns in Power BI that determine whether the KPI was met for each step by using the following logic:

If "Step [#] End Date" <= "Step [#] Due Date", "On Time"

If "Step [#] End Date" > "Step [#] Due Date", "Late"

(This formula ignores blanks as well)

Due to the nature of my organization, I cannot provide any actual data for privacy reasons. Hopefully this isn't a big deal, but here is an idea with fake data of how it is set up:

Project Name	Step 1 Due	Step 1 End	Step 1 Met KPI?	Step 2 Due	Step 2 End	Step 2 Met KPI?	Step 3 Due	Step 3 End	Step 3 Met KPI?	Step 4 Due	Step 4 End	Step 4 Met KPI?	Step 5 Due	Step 5 End	Step 5 Met KPI?
Project 1	2022-12-01	2022-11-17	On Time	2022-12-31	2023-01-03	Late	2023-02-27	2023-02-27	On Time	2023-03-03	2023-03-01	On Time	2023-03-25	2023-03-27	Late
Project 2	2023-01-23	2023-01-25	Late	2023-01-29	2023-01-28	On Time	2023-02-10	2023-02-09	On Time	2023-02-14	2023-03-01	Late	2023-04-05
Project 3	2023-02-20	2023-02-25	Late	2023-05-01
Project 4	2023-04-01	2023-03-15	On Time	2023-03-20	2023-03-20	On Time	2023-04-25

The visual would also need to be able to filter data by "End Date" so that it only shows the projects that completed a step within a certain date range. For example, one of my reports will only include data from March 2023, so only the step completions that happened in March 2023 would be included. I also need to generate reports that encapsulate data from this past quarter, as well as this past fiscal year (Apr-Mar).

The part that I'm really struggling with is figuring out how to display the distributions of "On Time" vs. "Late" with each column on the visual corresponding to a step. I think it has to do with the fact that the data is located in multiple columns? Here is a horrible MS Paint illustration (again, fake data) of how I'm being asked to display it:

Any guidance is appreciated. Hopefully this makes sense. Like I said, I'm totally new to Power BI and this forum, so please feel free to request additional information if this doesn't make sense. I realize I may have to reconfigure the way the data is collected, but that would hopefully be last resort, as this would affect everyone in the organization that inputs this data.

Thank you!

[DOLEARY85 · Accepted solution]

Hi,

You should be able to achieve this by unpivoting the step # kpi met columns in power query, this will place all the fields in one column and the field name will become the attribute you can use in the chart.

[brdmcdgll]

This is almost exactly what I need, thank you!

I ended up removing my calculated columns from the data view, inserting conditional columns in Power Query instead, then unpivoting them. However, this brings me to the issue with filtering by step completion date. Currently, it is showing all records since the beginning of the data set. I need to filter so that only "step completions" (end dates) that have happened within certain date ranges appear in the visual.

When I try to filter based off one column (for example, Step 1 End Date = on or after March 1, and on or before March 31), the rest of the data is filtered as well (any records with a Step 2/3/4/5 End Date occuring in March are filtered out if their Step 1 End Date is not within March).

I have tried unpivoting the five "End Date" columns, but this duplicates the record with one row containing the date and one row containing "On Time/Late", which skews my data in the visual. I think it could work if I could somehow get it from this:

Project Name	Step 1 End	Step 2 End	Step 3 End	Step 4 End	Step 5 End	Unpivoted Step	Unpivoted KPI Y/N
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 1 Met KPI?	On Time
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 2 Met KPI?	Late
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 3 Met KPI?	On Time
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 4 Met KPI?	On Time
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 5 Met KPI?	Late

To this:

Project Name	Step 1 End	Step 2 End	Step 3 End	Step 4 End	Step 5 End	Unpivoted Step	Unpivoted KPI Y/N?	End Date
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 1 Met KPI?	On Time	2022-03-01
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 2 Met KPI?	Late	2022-04-23
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 3 Met KPI?	On Time	2022-05-01
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 4 Met KPI?	On Time	2022-08-12
Project 1	2022-03-01	2022-04-23	2022-05-01	2022-08-12	2022-08-13	Step 5 Met KPI?	Late	2022-08-13

My thought is that if I were able to insert a new "End Date" column that listed each step's End Date on the same row as the corresponding step number, I could then filter the visual based off this new column to show only step completions within a certain range. Is this possible?

Thanks again, any help is appreciated.

[DOLEARY85]

Glad it's partially worked so far to create that end date column you can do it in power query with a custom column and some if statements.

try the below:

if Text.Contains([Unpivoted Step],"Step 1") then [Step 1 End] else if Text.Contains([Unpivoted Step],"Step 2") then[Step 2 End] else if Text.Contains([Unpivoted Step],"Step 3") then [Step 3 End] else if Text.Contains([Unpivoted Step],"Step 4") then[Step 4 End] else if Text.Contains([Unpivoted Step],"Step 5") then[Step 5 End] else null()

[brdmcdgll]

Wow, thank you so much. Works like a charm. This has been an IMMENSE help!