Forecasting in Power BI: DAX

Time Series Analysis in Power BI

Kevin Barlow

Data Analytics Professional

Context and importance

Built-in capabilities are a great start but are limited.

Tuning and optimization aren't really an option and are restricted to base parameters.

Other alternatives

  • Programming Languages (Python / R)

  • Excel

  • DAX

Power BI Forecast Options

Time Series Analysis in Power BI

DAX forecasting basic principles

With DAX, we can approximate the behavior of a dataset over our given timeframe.

  1. Identify the last point of data.
  2. Calculate the data trend (geometric mean).
  3. Project into the future.

A geometric mean provides an average for an exponentially changing dataset.

Forecasting with Geometric Mean

Time Series Analysis in Power BI

CAGR

Compound Annual Growth Rate (CAGR)

CAGR Formula

While this formula is for annual growth, the same logic can be applied to other time intervals (e.g. monthly growth rate)

Time Series Analysis in Power BI

Using CAGR to forecast

Forecasting future values

Future value calculation

LV = Last Value

n = number of periods in the future

You could test this formula on your data by hindcasting!

Time Series Analysis in Power BI

Let's practice!

Time Series Analysis in Power BI

Preparing Video For Download...