Project Tracking Unleashed: Build S-Curves with Power BI (Part 2)

Welcome to Part 2 of our journey in creating an S-Curve in Power BI!  If you’ve made it this far, you’re already on the right track to mastering this essential project tracking visual.

In Part 1, we laid the groundwork by structuring our data model correctly. We discussed why a Date Dimension Table is crucial, how it ensures a continuous timeline, and why relying solely on the date fields in your project table can lead to all sorts of issues. We also explored handling multiple date fields and tackled Power BI’s limitation of allowing only one active relationship at a time.

If you missed Part 1, you can catch up here Mastering Project Tracking: Build S-Curves with Power BI – Part 1.

Now, it’s time to bring our S-Curve to life! One of the biggest challenges in project tracking is switching between Forecast and Actual dates dynamically. This is where USERELATIONSHIP comes into play.

Unleashing the Power of USERELATIONSHIP

USERELATIONSHIP is a powerful function in Power BI that allows you to activate an inactive relationship between tables within your data model. By default, only one active relationship can exist between two tables, while others remain inactive.

This function is particularly useful when working with project data that contains multiple date columns. By using USERELATIONSHIP, we can seamlessly switch between these different date fields to build a comprehensive S-Curve.

USERELATIONSHIP(<column1>, <column2>)

• <column1>: This is the first column involved in the relationship, typically from the table where the measure or calculation is being evaluated.
• <column2>: This is the second column involved in the relationship, typically from the related table.

The USERELATIONSHIP function is used within the CALCULATE function to activate an inactive relationship for the duration of the calculation. Based on the data model explained in Part 1, where the Actual Start Date is related to the Date Dimension Table through an inactive relationship, here’s an example of how we can use it to count the total number of activities that have actually started:

Count Activities Actual Started = 
 CALCULATE(
 COUNTROWS(
 FILTER(
 'Facts_Activities Schedule',
 'Facts_Activities Schedule'[Start Status] = 1
 )
 ),
 USERELATIONSHIP('Facts_Activities Schedule'[Date_Current Start], Dim_Date[Date])
 )

This measure calculates the number of activities that have actually started by counting rows where the ‘Start Status’ is 1 (indicating that the activity has started in my dataset). It leverages an inactive relationship between the ‘Date_Current Start’ column in the ‘Facts_Activities Schedule’ table and the ‘Date’ column in the ‘Dim_Date’ table to ensure accurate analysis.

By using USERELATIONSHIP, you tell Power BI: “Hey, for this calculation, use Actual Completion Date instead of the default Forecast Completion Date.” This is super important because without it, our S-Curve might show incorrect trends, leading to poor decision-making.

On the other hand we have the count of activities forecasted, where we do not need to use USERELASHIONSHIP because the relationship with the Date Dimension Table is already active:

Count Activities Forecast Start = COUNTA('Facts_Activities Schedule'[Date_Forecast Start])

The COUNTA function in this DAX measure is counting the number of non-blank entries in the column Date_Forecast Start from the Facts_Activities Schedule table.

Calculating Cumulative Totals

To kick off the process of building your S-Curves, the first step is to create two new measures that will capture the cumulative totals for both Forecast Start and Actual Start dates. By calculating these cumulative totals, we’ll be able to visualise the progress of your project over time. These measures will form the foundation of your S-Curves.

Cumulative totals are running sums that add up values over time. For instance, they show the ongoing total of tasks started in a project, giving a clear view of progress at each point in time.

Cumulative Count Activities Forecast Start =
CALCULATE(
    [Count Activities Forecast Start],
        FILTER(
        ALLSELECTED('Dim_Date'[Date]),
        'Dim_Date'[Date]<= MAX('Dim_Date'[Date])
        )
    )

The DAX for Cumulative Count Activities Forecast Start calculates the cumulative total of planned activities starting over time. It uses the CALCULATE function to modify the context of evaluation, focusing on the measure Count Activities Forecast Start, which counts the planned start activities.

The FILTER function is employed to create a filter context, ensuring that only dates up to the maximum selected date are considered.

By using ALLSELECTED(‘Dim_Date'[Date]), it takes into account all selected dates from the ‘Dim_Date’ table, while the condition ‘Dim_Date'[Date] <= MAX(‘Dim_Date'[Date]) ensures that only dates up to the current maximum date in the context are included. This measure provides a cumulative total of planned activities starting over time, up to the maximum date in the selection.

Cumulative Count Activities Actual Started =
IF(
    MAX('Dim_Date'[Date]) <= DATE(2023, 12, 31),
    CALCULATE(
       [_Count_Activites_Actual Started],
        FILTER(
            ALLSELECTED('Dim_Date'[Date]),
            'Dim_Date'[Date] <= DATE(2023, 12, 31) &&
            'Dim_Date'[Date] <= MAX('Dim_Date'[Date])
        )
    ),
    BLANK()
)

Cumulative Count Activities Actual Started calculates the total number of activities started over time, but only up to and including 31 December 2023, which is the cut-off date for the report (though it could be any other date). This prevents your S-Curve for the Actual Started from extending as a flat line beyond the point where there’s no more data available.

It uses the IF function to check if the maximum date in the current context is within this range. If it is, the measure calculates the cumulative total using CALCULATE and FILTER functions.

The FILTER function includes only dates up to the maximum selected date and 31 December 2023. If the date exceeds 31 December 2023, the measure returns a blank value, ensuring no data is shown beyond this point. This way, it creates a cumulative curve that stops at the specified end date, providing a clear and concise visualisation of the data within the desired timeframe.

Building the S-Curve

To effectively visualise your S-Curve, start by selecting the Line Chart, which is ideal for displaying data over time. Then you need to configure the axis:

X-Axis: Add the column with consecutive dates from the Date Dimension table. This ensures that your timeline is accurately represented.

Y-Axis: Add the Cumulative Count measures for both Activities Forecast Start and Actual Start. This will allow you to see the progression of your project’s activities over time.

Enhancing Accessibility

After setting up the axes, we need to configure the visual to ensure it’s accessible for all users. Here are some key considerations:

Use Clear Titles and Labels

• Your chart title should be clear, e.g., “Project Progress: Forecast vs Actual Completion.”
• Axis labels should be descriptive. Don’t just say “Date” or “Tasks,” be specific.

 Don’t Rely on Colour Alone

• Colour-coded lines for forecast vs actual are great, but what about colour-blind users?
• Use different line styles (e.g., solid vs dashed) or data markers (circles, squares) to differentiate them.

Choose an Appropriate Font Size

• Tiny text = unreadable charts.
• Stick to 12pt or larger for reports and 14pt+ for presentations.

Add Alt Text for Screen Readers

• People using screen readers need alternative text that describes the chart.
• Example: “Line chart showing project progress over time. The solid blue line represents forecasted task completions, while the dashed orange line represents actual completions.”

Ensure Sufficient Contrast

• Make sure text and lines stand out against the background.
• Ensure the contrast ratio between text and background is at least 4.5:1 for readability, and a minimum of 3:1 for non-text elements against the background. This helps make your visual more accessible to all users, including those with visual impairments.

Provide a Data Table Alternative

• Not everyone prefers visuals. Some people need a data table view for accessibility.
• In Power BI, viewing the underlying data table is already a built-in feature, making it compatible with screen readers and accessible to all users. To ensure everyone benefits from this, we need to let our users know that they can easily access the data behind the charts by right-clicking on the visual and selecting the “Show Data” option, or using the keyboard shortcut Alt + Shift + F11.
• Please note that not all custom visuals in Power BI offer the option to view the underlying data table directly. So when is the case, we need to include an alternative way to see the numbers behind the chart.

It’s your turn!

Creating an S-Curve is about structuring your data correctly, using relationships effectively, and making sure your visual is clear, accurate, and accessible. Now that you know how to:

• Model your data properly
• Use USERELATIONSHIP to handle multiple dates
• Create cumulative totals using DAX
•  Apply accessibility best practices

With these steps, you’re ready to build an effective, professional, and user-friendly S-Curve that project managers will love! And the best part? These same principles can be applied to create S-Curves for Milestones or to track Planned Value against Earned Value. Now, it’s your turn to give it a try and put these insights into practice. Show off your data visualisation skills and impress your stakeholders!

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.