DAX Calculation for Time Series Forecast

Updated: If your are looking for the Power BI Desktop file with all the logic in it, please go this page: https://www.obvience.com/time-series-forecasting

It has the file and step by step instructions.

Today, we're diving back into Power BI and DAX calculations to explore how to implement time series analysis. Unlike the usual methods that rely on built-in forecast feature of the Power BI line chart, we're focusing on doing everything with DAX. This gives you more flexibility to create forward-looking reports and dashboards. In this video, you'll learn: The basics of time series analysis in Power BI.

• How to create a forecast measure using DAX.

• The process of breaking down data into trend and seasonality components.

• Practical implementation of these concepts with step-by-step DAX code.

Whether you're a beginner or looking to refine your skills, this video will show you how to leverage DAX for powerful time series analysis.

First we need to calculate the Trend component of the Time Series based Forecast:

Sales Forecast - Trend = 
VAR CurrentYear = MAX('Date'[Year])
VAR PriorYears = 
    SUMMARIZE(
        FILTER(
        ALL('Date'),
        'Date'[Year] >= CurrentYear-1
        ), 
        'Date'[Year], 'Date'[Week])
        
VAR WeeklyData = 
    FILTER(
		CALCULATETABLE(
			ADDCOLUMNS(
			PriorYears,
			"Revenue", [Revenue]), ALL('Date')
    	),
		[Revenue]<>BLANK())
VAR Stats = 
    LINESTX(
        WeeklyData,
        [Revenue],
        [Week]
    )
VAR Slope = MAXX(Stats, [Slope1])

VAR Intercept = 
SELECTCOLUMNS(Stats, "Intercept", [Intercept])

var WeeksCount = COUNTROWS(PriorYears)

RETURN Slope * (MAX('Date'[Week])+WeeksCount) + Intercept

Now we need to calculate the Detrended revenue:

DetrendedRevenue = 
[Revenue] - [Sales Forecast - Trend]

Now let’s calculate Seasonality:

Sales Forecast - Seasonality = 
VAR CurrentYear = MAX('Date'[Year])
VAR CurrentWeek = MAX('Date'[Week])
VAR PriorYears = 
    SUMMARIZE(
        FILTER(
            ALL('Date'),
            'Date'[Year] < CurrentYear 
            && 'Date'[Week] = CurrentWeek
        ),
        'Date'[Year],
        'Date'[Week],
        "_Sales", [DetrendedRevenue]
    )
RETURN 
    AVERAGEX(PriorYears, [_Sales])

And now we put it all together:

Sales Forecast = 
[Sales Forecast - Seasonality] + [Sales Forecast - Trend]