In this article, I’ll show you how to create a dynamic slope graph in Power BI that displays year-over-year percentage changes. You’ll only need two DAX measures to build this visualization, plus an optional third measure for conditional formatting.

What is a Slope Graph?

A slope graph is a type of line chart that visualizes changes between two points in time or two different states. By focusing on only two points per category, it simplifies the comparison of both absolute and relative changes. Slope graphs are most effective when used with a limited number of categories.

The Static Slope Graph

A simple approach to creating a slope graph in Power BI involves filtering out unwanted data points from the x-axis. For instance, to compare revenue between 2021 and 2024, you could exclude 2022 and 2023. However, this static approach becomes problematic when using a year slicer, as the visual won’t dynamically adjust to the selected years.

---

The Dynamic Slope Graph

To overcome this limitation and create a dynamic slope graph that responds to slicer selections, we can implement the following DAX formula:

Sales_Slope Graph = 
VAR FirstYear = MINX(ALLSELECTED(DimDate), DimDate[Year]) -- First selected year
VAR LastYear = MAXX(ALLSELECTED(DimDate), DimDate[Year]) -- Last selected year
RETURN
    IF(
        SELECTEDVALUE(DimDate[Year]) = FirstYear || SELECTEDVALUE(DimDate[Year]) = LastYear,
        SUM(Candy_Sales[Sales]),
        BLANK() -- Ensure intermediate years return BLANK()
    )

The provided DAX formula is designed to create a slope graph that compares the total sales between the first and last selected years. Here’s a breakdown of the formula:

1. Identifying First and Last Years:

• FirstYear and LastYear variables use MINX and MAXX functions to determine the minimum and maximum years within the current filter context. This ensures that the formula adapts to the user’s selections in slicers or filters.

2. Conditional Calculation:

• The IF statement checks if the current year is either the first or the last year.
• If it’s the first or last year, the SUM(Candy_Sales[Sales]) is calculated to get the total sales for that year.
• If it’s any other year, BLANK() is returned, effectively filtering out those years from the visualization.

After creating the DAX measure, create a line chart. Set the x-axis to DimDate[Year] (formatted as categorical) and the y-axis to the Sales_Slope Graph measure. This will display the values for the first and last years, highlighting the change over time.

---

The Importance of Adding Percentage Comparison in a Slope Graph

While it’s easy to identify the largest absolute changes in values by looking at the steepness of the slopes, this doesn’t necessarily indicate the greatest percentage change. To provide a more accurate comparison, we can add percentage change labels to each category on the slope graph. This will allow us to see the relative change in values, rather than just the absolute difference.

Revenue_YoY_Change_Graph = 
VAR FirstYear = 
    CALCULATE(
        MIN(DimDate[Year]),
        ALLSELECTED(Candy_Sales)
    )
VAR LastYear = 
    CALCULATE(
        MAX(DimDate[Year]),
        ALLSELECTED(Candy_Sales)
    )
VAR LastYearValue = 
    CALCULATE(
        SUM(Candy_Sales[Sales]),
        DimDate[Year] = LastYear
    )
VAR FirstYearValue = 
    CALCULATE(
        SUM(Candy_Sales[Sales]),
        DimDate[Year] = FirstYear
    )
VAR YoYChange = 
    IF(
        FirstYearValue > 0,
        DIVIDE(LastYearValue - FirstYearValue, FirstYearValue, 0),
        0
    )
RETURN 
    IF(
        SELECTEDVALUE(DimDate[Year]) = FirstYear, 
        BLANK(), 
        IF(
            YoYChange > 0, 
            "▲ " & FORMAT(YoYChange, "0.0%"), 
            IF(
                YoYChange < 0, 
                "▼ " & FORMAT(YoYChange, "0.0%"), 
                BLANK()
            )
        )
    )

The primary goal of this DAX formula is to calculate the year-over-year (YoY) change in revenue and display it in a visually appealing format, including up and down arrows to indicate the direction of change.

Step-by-Step Breakdown:

1. Identifying First and Last Years:

• FirstYear and LastYear variables use CALCULATE and ALLSELECTED to determine the minimum and maximum years within the current filter context. This ensures that the calculation is based on the selected time period.

2. Calculating Revenue for First and Last Years:

• LastYearValue and FirstYearValue calculate the total revenue for the last and first years, respectively.

3. Calculating YoY Change:

• The YoYChange variable calculates the percentage change between the last and first years. It handles potential division by zero errors by using the DIVIDE function with a default value of 0.

4. Formatting the Output:

The RETURN statement conditionally formats the output based on the YoY change:

• If the current year is the first year, a blank value is returned (Note: To ensure that the percentage change is only displayed for the last year, the first year’s data point is omitted from the slope graph).
• If the YoY change is positive, an upward arrow (▲) is displayed along with the formatted percentage change.
• If the YoY change is negative, a downward arrow (▼) is displayed along with the formatted percentage change.
• If there’s no change, a blank value is returned.

---

Optional: Conditional Formatting

To further enhance the visualization, we can apply conditional formatting to the percentage comparison labels. Positive changes can be shown in green, and negative changes in red.

CF_Revenue_YoY Change = 
VAR FirstYear = 
    CALCULATE(
        MIN(DimDate[Year]),
        ALLSELECTED(Candy_Sales)
    )
VAR LastYear = 
    CALCULATE(
        MAX(DimDate[Year]),
        ALLSELECTED(Candy_Sales)
    )
VAR LastYearValue = 
    CALCULATE(
        SUM(Candy_Sales[Sales]),
        DimDate[Year] = LastYear
    )
VAR FirstYearValue = 
    CALCULATE(
        SUM(Candy_Sales[Sales]),
        DimDate[Year] = FirstYear
    )
VAR YoYChange = 
    IF(
        FirstYearValue > 0,
        DIVIDE(LastYearValue - FirstYearValue, FirstYearValue, 0),
        0
    )
RETURN 
    IF(
        YoYChange > 0, 
        "#6A994E",  -- Green for positive change
        IF(
            YoYChange < 0, 
            "#BC4749",  -- Red for negative change
            BLANK()     -- No color if YoYChange is 0 or undefined
        )
    )

By applying these two measures, we will achieve the following result:

---

Thank you for reading!

I hope this case study provided valuable insights and sparked your interest. Please feel free to connect with me on LinkedIn or via email if you have any questions or would like to discuss this further.

Download .pbix files here.