Calculating a linear extrapolation (or Trend) in DAX

Author:Murphy | View: 27970 | Time: 2025-03-22 19:10:56

Calculating a trend helps us recognize if our business is heading in the right direction. While other programming languages can offer built-in functionalities to calculate this, DAX doesn't, and we must do it on our own.

Introduction

Once upon a time, a client asked me to calculate a linear extrapolation of his data to show the trend based on past data.

This is halfway between descriptive and predictive analytics, as it doesn't employ Machine learning or AI techniques.

It will not account for seasonality or other influencing factors in the data.

The method described here uses the existing data and linearly calculates the extrapolation for all subsequent months, quarters, or other periods.

Let's look into it.

The Data and scenario

Using the well-known Contoso retail Data (Source in the References Section below), I want to analyze the number of Customers buying products from my company.

Assuming that the current date is April 2022, I see the following data for the first quarter of Sales in the year:

Figure 1 – Scenario looking at the first quarter of 2022 (Figure by the Author)

The Measure used is a simple YTD Measure with a DISTINCTCOUNT() of the CustomerKey columns:

Online Customer Count YTD = 
VAR YTDDates = DATESYTD('Date'[Date])

RETURN
CALCULATE(
    DISTINCTCOUNT('Online Sales'[CustomerKey])
    ,YTDDates
    )

From this point, I want to calculate the linear progression of the customer count, which is the number of people who buy stuff at my company each month until the end of the year.

Calculation of the linear extrapolation for the current year

Mathematically, the method to calculate the linear extrapolation is the following:

In March, the Customer Count YTD is 1396.
The month number for March is 3.
The Average is 1396 / 3 = 465.33 à This is the Average YTD value per month.
To calculate the result for April, I multiply the Average by 4 = 1861.33.
For May, I multiply the Average by 5 = 2326.65.
And so on until December.

I use this Dax code for a Measure to implement the logic above:

Linear extrapolation Customer Count YTD = 
// Get the number of months from the start of the year to the last month with Sales data
VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
                                    ,RELATED('Date'[Month])
                                    )
                            ,ALLEXCEPT('Date', 'Date'[Year])
                            )

// Get the last, or current, month
VAR MaxMonth = MAX('Date'[Month])

// Get the Customer Count YTD
VAR CustomerCountYTD = [Online Customer Count YTD]

RETURN
// Calculating the extrapolation
IF(LastMonthWithData >= MaxMonth
    ,CustomerCountYTD
    ,(CustomerCountYTD / LastMonthWithData) * MaxMonth
    )

The YTD Measure ensures that I always get a result for each month until the end of the year (As you will see in the following screenshot).

First, I get the number for the last month with data from the fact table ‘Online Sales' (Variable: LastMonthWithData).

Next, I get the number for the current month, a.k.a., for the month in the current filter context (Variable: MaxMonth).

Then, I get the result of the YTD Measure, which is the basis for calculating the result for each month (Variable: CustomerCountYTD).

Lastly, after the RETURN statement, I calculate the final result.

The IF ensures that the Measure returns the current YTD value if such a value exists (LastMonthWithData >= MaxMonth).

Otherwise, it divides the YTD value with the last month with data (March à 3), multiplying the result by the number of the current month.

The result looks like this:

Figure 2 – Result of the first Measure to calculate the Linear extrapolation starting with the results of March 2022 (Figure by the Author)

When the year progresses, the Measure returns the existing numbers but continues to extrapolate the results until the end of the year.

Therefore, when we move forward to July (The last transaction is at the end of June), we see this result:

Figure 3 – Results by the end of June 2022 with the extrapolation (Figure by the Author)

You can see that the extrapolation changes as the year progresses, as more data is added to the logic.

As a result of the Measure, we can remove the original [Online Customer Count YTD] Measure and describe the shown data by adding a dynamic title like this:

YTD and extrapolation Title = 
    VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
                                    ,RELATED('Date'[Month])
                                    )
                            ,ALLEXCEPT('Date', 'Date'[Year])
                            )
    VAR CurrentYear = SELECTEDVALUE('Date'[Year])

RETURN
    "Online Customer Count YTD by Month " & CONVERT(CurrentYear, STRING) & "/" & LastMonthWithData & " (with extrapolation until the end of the year)"

To get the following result:

Figure 4 – Results by July 2022, including the dynamic title.

If the source Measure isn't a YTD Measure, the logic changes, as the Base Measure will return only data until the last Transaction (June 2022 in our case).

Therefore, you need to develop a Measure that always returns a value by the last date with data for the entire year (Here is an example for Online Sales):

Linear extrapolation Online Sales = 
// Get the number of months since the start of the year
VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
                                    ,RELATED('Date'[Month])
                                    )
                            ,ALLEXCEPT('Date', 'Date'[Year])
                            )

VAR CurrentYear = SELECTEDVALUE('Date'[Year])

// Get the last month
VAR MaxMonth = MAX('Date'[Month])

// Get the Customer Count YTD
VAR LastOnlineSales = CALCULATE([Sum Online Sales]
                                ,REMOVEFILTERS('Date')
                                ,'Date'[Month] = LastMonthWithData
                                ,'Date'[Year] = CurrentYear
                                )

RETURN

// Calculating the extrapolation
IF(LastMonthWithData >= MaxMonth
    ,[Sum Online Sales]
    ,(LastOnlineSales / LastMonthWithData) * MaxMonth
    )

The key here is to use the Month and the Year columns to ensure that the current result is calculated.

Without the year, the Measure would have calculated the Sales not for June 2022 only but for all years, resulting in wrong results.

The remaining of the Measure is the same as before.

Calculation of the extrapolation and showing the current results side-by-side

But what when we want to see the extrapolation and the original results side by side, to see the trend of the past data and the extrapolation?

In this case, we need to change the Measure to this:

Linear extrapolation Customer Count YTD trend = 
// Get the number of months since the start of the year
VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
                                    ,RELATED('Date'[Month])
                                    )
                            ,ALLEXCEPT('Date', 'Date'[Year])
                            )

VAR CurrentYear = SELECTEDVALUE('Date'[Year])

// Get the last month
// Is needed if we are looking at the data at the year, semester, or quarter level
VAR MaxMonth = MAX('Date'[Month])

// Get the Customer Count YTD
VAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]
                                ,REMOVEFILTERS('Date')
                                ,'Date'[Year] = CurrentYear
                                ,'Date'[Month] = LastMonthWithData
                                )

RETURN
// Calculating the extrapolation
(LastCustomerCountYTD / LastMonthWithData) * MaxMonth

Now, I get the current Year (Variable: CurrentYear) and always calculate the last known YTD value for all months (Variable: LastCustomerCountYTD).

The IF() is unnecessary now, as I always want to display the extrapolation:

Figure 5 – Result of the extrapolation as a trend (Figure by the Author)

You're right if this feels like something already that existed in Power Bi.

The older Power BI versions used to have an Analytics pane for some Visuals:

Figure 6 – Analytics pane in older Power BI versions (Figure by the Author)

But this feature is long gone and replaced with new features in the Build pane.

I added some remarks about this feature in the Conclusion section below.

Calculation of the Trend based on the past 10 months

Such a calculation makes only sense when the Base Measure can be calculated over year boundaries.

For example, the number of Orders over the last 10 months.

We want to take this result and use this information to extrapolate to the end of the year.

Such a calculation is helpful to see whether the cumulated number of Orders changes over time.

I have a Measure to count the Orders:

Online Order Count = DISTINCTCOUNT('Online Sales'[SalesOrderNumber])

Now, I must add a date filter to count the orders over the last 10 months:

Order Count (over 10 months) = 
    CALCULATE([Online Order Count]
                ,DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -10, MONTH)
                )

The result is the following:

Figure 7 – Order count over the last 10 months (Figure by the Author)

As you can see, the Measure returns meaningful data only from the tenth month (October 2021) until the month with the last sales, June 2022. Before and after, the cumulative result doesn't include 10 months.

I should include a condition not to return any result if there are no transactions (Sales). Therefore, I add the following condition to it:

Order Count (over 10 months) = 
IF(NOT ISBLANK([Online Order Count])
    ,CALCULATE([Online Order Count]
                ,DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -10, MONTH)
                )
    )

Now, the result looks better:

Figure 8 – Modified order count over the last 10 months (Figure by the Author)

Next, I can add logic similar to that used before to calculate the trend.

But with a big difference: As the base value at the beginning of a year is not zero, I must consider the first and last values within the year.

Then, I must divide the difference between the first and the last values within the year by the number of months for the last value (for example, divide by 6 when the last data point is in June).

Next, I must add this difference (multiplied by the current month – 1) to the starting value:

Linear extrapolation order count over 10 months = 
    // Get the number of months since the start of the year
    VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
                                        ,RELATED('Date'[Month])
                                        )
                                ,ALLEXCEPT('Date', 'Date'[Year])
                                )

    VAR CurrentYear = MAX('Date'[Year])
    VAR CurrentMonth = SELECTEDVALUE('Date'[Month])

    // Get the last month
    // Is needed if we are looking at the data at the year, semester, or quarter level
    VAR MaxMonth = MAX('Date'[Month])

    // Get the Customer Count for the last month
    VAR FirstOrderCount10m = CALCULATE([Order Count (over 10 months)]
                                    ,REMOVEFILTERS('Date')
                                    ,'Date'[Year] = CurrentYear
                                    ,'Date'[Month] = 1
                                    )

    // Get the Customer Count for the last month
    VAR LastOrderCount10m = CALCULATE([Order Count (over 10 months)]
                                    ,REMOVEFILTERS('Date')
                                    ,'Date'[Year] = CurrentYear
                                    ,'Date'[Month] = LastMonthWithData
                                    )

    VAR DiffPerMonth = (LastOrderCount10m - FirstOrderCount10m) / LastMonthWithData

RETURN
    FirstOrderCount10m + (DiffPerMonth * (CurrentMonth - 1))

I must subtract one from the multiplication to get the starting point right.

This is the result over two years:

Figure 9 – Linear extrapolation, or trend line for Order count over 10 months (Figure by the Author)

Interestingly, the slope changes between 2021 and 2022.

This could indicate a slowdown in sales, and I must take action to improve the situation.

Conclusion

The solution shown here is helpful for either calculating a linear extrapolation or a trend analysis.

In the end, both are the same.

As mentioned above, there used to exist a solution to add a Trend line in past versions of Power BI:

Solved: Add a trend line to a line chart Power BI 2024 – Microsoft Fabric Community

And here:

How To Use Trend Line in Analytics Pane in Power BI?

However, all these solutions rely on switching to a categorical X-axis, which works only when using a numerical or Date field on the X-axis.

It doesn't work with text fields like the Month name I used.

Therefore, it doesn't work in all scenarios.

The solution presented here always works, regardless of what kind of fields you use on the X-axis.

Moreover, you can add any custom code to the calculation to achieve any desired result.

I hope you have learned something new here.

Yes, it could be easier to add R or Python code to achieve the same, but not everyone knows one of these languages. So, we stick to what we know and do the best we can with it.