If you’ve ever worked in Project Controls or PMO reporting (like I have!), you’ve probably had a project manager ask, “Can you show me progress versus plan?” And more often than not, they’re looking for an S-Curve.
S-Curves are one of the most requested visuals in project tracking because they provide a clear, easy-to-understand snapshot of how a project is progressing. They help answer crucial questions: Are we on track? Are we falling behind? Do we need to take action?
Recognising the immense value of this visual in Project Controls, I’m here to guide you through the steps to create it in Power BI. Get ready to transform your project data into a powerful S-Curve with clear, straightforward instructions that will help you make the most of this essential tool. Let’s dive in and bring your data to life!
Understanding S-Curves
An S-Curve is basically a line chart that shows cumulative project progress over time. It’s called an “S-Curve” because of its typical shape:
- Slow start: At the beginning of a project, not much happens, things are being set up.
- Rapid growth: As work picks up, progress accelerates.
- Slow finish: Towards the end, things slow down as the last few tasks get completed.
S-Curves are invaluable in project management as they track progress, revealing if things are on schedule. They help spot issues early, highlighting when actual progress lags behind the forecast. They aid in decision-making, allowing project managers to intervene before delays worsen. Additionally, they simplify reporting, providing stakeholders with a clear overview at a glance.
Sounds great, right? Do you know what’s better? Power BI is a great tool to build your S-Curves but to make it work, you need well-structured data model.
If you want to learn more about S-Curves, visit here. But now let’s get started!
Getting Your Data Ready for an S-Curve
Before we start plotting fancy charts, let’s talk about how to set up your data correctly. If our data isn’t structured well, our S-Curve will be more of a “?”-Curve (as in, What on earth is this showing?).
The best way to structure your data is to organise it into two key tables: Facts and Dimensions. If you are not familiar with these concepts, check out the Microsoft Learning page Model data with Power BI – Training | Microsoft Learn.
Facts Table (Project Data) – This contains all your key project details, including:
- Tasks Ids
- Forecast Completion Dates (when tasks should be completed, it can be the start and/or the finish date)
- Actual Completion Dates (when tasks were actually completed, it can be the start and/or the finish date)
Dimension Table (Date Table) – This contains a list of all the dates in your project timeline. It acts as a backbone for your time-based analysis. The list should contain unique consecutive days.
Please note we may need to use other dimension tables depending on the report requirements, but for this example, I will only focus on tables listed above.
If you’re thinking, “Why can’t I just use the two date columns in my Facts Table instead of creating a separate Date Dimension Table?”, I totally get it, I have been asked this countless times! It seems easier at first. But trust me, skipping the Date Dimension table is like trying to build a house without a foundation and it will cause problems later.
Let’s say we try to build an S-Curve using only the Forecast Completion Date and Actual Completion Date columns from our Facts Table. Here’s what can go wrong:
We Can’t Create a Proper X-Axis for Time-Based Analysis
Without a separate Date Table, our X-axis (timeline) will be based only on the dates that exist in the Facts Table.
If some dates are missing (e.g., no tasks were completed on certain days), our chart won’t have a continuous timeline. It will have gaps, making the S-Curve inaccurate.
We Can’t Compare Forecast vs Actual Properly
Since each row in the Facts Table has two different date columns (Forecast and Actual Completion), Power BI doesn’t know which one to use as the X-axis.
If we plot both on the same chart without a Date Dimension table, the system treats them as separate, unrelated date fields.
Instead of one smooth S-Curve, we’ll likely end up with two disconnected sets of data points. One based on Forecast dates and one on Actual dates, making comparisons difficult.
Filtering and Slicing Won’t Work as Expected
Let’s say we want to filter your chart to show progress by quarter. Without a Date Table, Power BI doesn’t understand how Forecast and Actual dates relate to each other across months.
The filters might only apply to one column at a time, making the data unreliable.
Time Intelligence Functions Won’t Work
Want to calculate rolling averages, cumulative progress, or year-to-date completions? Power BI’s time intelligence functions (like TOTALYTD, DATESBETWEEN, etc.) only work if we have a proper Date Dimension table.
When we introduce a Date Dimension Table, we create a single timeline that both Forecast and Actual Completion Dates can connect to. This solves all the issues above.
Handling Multiple Date Columns in the Facts Table
Now, here’s where it gets interesting. Our Facts Table has 4 different date columns:
- Forecast Start Date
- Actual Start Date
- Forecast Finish Date
- Actual Finish Date
And we want to connect all these columns to our Dimension table. But Power BI only allows one active relationship at a time. So, what can we do?
Power BI works based on context, meaning it automatically applies the active relationship in our data model. If the active relationship is with Forecast Completion Date, but we need to analyse actual completions, Power BI won’t know unless you force it.
That’s where USERELATIONSHIP comes in. This function lets us switch to an inactive relationship when needed. Stay tuned for part two of this post, where I’ll unravel the secrets of USERELATIONSHIP, and guide you through calculating cumulative totals, demonstrate how to plot the perfect S-Curve, and ensure everything is accessible. You won’t want to miss it!
Thank you for joining me on this journey. Until next time, let’s keep crafting accessible insights that make a difference!
Smart Frames is on YouTube and LinkedIn now!
Check out Accessible BI for practical Power BI tutorials and tips on making data accessible to everyone. Subscribe here: Accessible BI YouTube Channel.
Follow on LinkedIn.
