Skip to content
Analytical AntsAnalytical Ants

Time Series Decomposition and Autocorrelation in Power BI with Python

By William Rodriguez
InsightsPower BIPython

Give report users a way to explore any metric for trend and seasonality — dynamically, at any grain — by pairing Power BI field parameters with a Python visual running statsmodels.

TL;DR

Overview

Functionality

Allows end users to dynamically explore their data for seasonality and trends based on any attribute or metric available to them in their Power BI report.

Features

Technical Definitions

Implementation

Power BI

Create a new field parameter based on a calendar table that has the needed dates as a time series. For example, on your calendar table you will need a Calendar[First Day of Quarter] column that repeats the first day of the quarter for every quarter in your date table. Then update your new field parameter table with the DAX below. Once complete, rename the fourth column to Seasonality[Seasonal Value].

Seasonality = {
    ("Yearly", NAMEOF('Calendar'[First Day of Year]), 0 , 1),
    ("Quarterly", NAMEOF('Calendar'[First Day of Quarter]), 1 , 4),
    ("Monthly", NAMEOF('Calendar'[First Day of Month]), 2 , 12),
    ("Weekly", NAMEOF('Calendar'[First Day of Week]), 3 , 7) ,
    ("Daily", NAMEOF('Calendar'[Date]), 4 , 365)
}

You will need a new measure for your seasonality table:

Selected Seasonality = SELECTEDVALUE( Seasonality[Seasonal Value] , 12 )

Now the column Seasonality[Seasonality] will be used as a filter on your report.

Next, bring the following columns and measures into a Python visual: Seasonality[Seasonality], [Your Measure], and [Selected Seasonality]. Lastly, filter the visual so [Your Measure] is not blank.

Python

Insert the following Python scripts into your Python visual. First:

  1. Follow the prerequisites for working with Python in Power BI (Microsoft — Python in Power BI Desktop).
  2. Load the following modules to your Python environment:
pip install pandas matplotlib numpy statsmodels

Time Series Decomposition (TSD)

import pandas as pd
import matplotlib.pyplot as plt
from statsmodels.graphics.tsaplots import plot_acf, plot_pacf
from statsmodels.tsa.seasonal import seasonal_decompose, STL
from statsmodels.tools.sm_exceptions import ValueWarning
import warnings
import numpy as np
import os
 
'''
In Power BI, assume the dataset looks like:
     0 - Date Column
     1 - Data Column
     2 - Selected Seasonality Column
'''
try:
    # If 'dataset' exists, we're likely in the Power BI environment
    df = dataset
except NameError:
    # If dataset does not exist, then run locally
    script_dir = os.getcwd()  # current working directory
    file_name = "Monthly data.csv"
    file_path = os.path.join(script_dir, file_name)
    df = pd.read_csv(file_path)
 
# Identify column names
date_column = df.columns[0]          # e.g., "Monthly" or "Quarterly" date strings
data_column = df.columns[1]          # e.g., "SUM Data"
seasonality_column = df.columns[2]   # e.g., "Selected Seasonality"
 
# Clean Data
df[date_column] = pd.to_datetime(df[date_column]) # Convert date column to datetime
df.sort_values(date_column, inplace=True) # Sort by date just in case
df.set_index(date_column, inplace=True) # Set index to date column
df[data_column] = df[data_column].fillna(df[data_column].mean())
 
# Grab the first "Selected Seasonality" from the dataset
selected_seasonality = df[seasonality_column].iloc[0]
 
# Dynamic Formatting
visual_width = 800  # Default width in pixels
visual_height = 600  # Default height in pixels
try:
    if 'width' in dataset.columns and 'height' in dataset.columns:
        visual_width = dataset['width'].iloc[0]
        visual_height = dataset['height'].iloc[0]
except NameError:
    visual_width = 800
    visual_height = 600
 
# Aspect ratio scaling
aspect_ratio = visual_width / visual_height
base_size = min(visual_width, visual_height) / 100  # Scale based on smaller dimension
figsize = (base_size * aspect_ratio, base_size)
 
# Dynamic font scaling
font_scale = base_size / 8
plt.rcParams.update({'font.size': 10 * font_scale})
 
## ---------------------  TSD ---------------------
 
# Choose decomposition method based on data availability
if len(df) >= 2 * selected_seasonality:  # Check if we have at least two cycles
    result = seasonal_decompose(df[data_column], model='additive', period=selected_seasonality)
else:
    print("Using STL for decomposition due to insufficient cycles for traditional method.")
    result = STL(df[data_column], period=selected_seasonality).fit()
 
# Calculate R-squared
y = df[data_column]
y_hat = result.trend + result.seasonal
r_squared = 1 - (np.sum((y - y_hat)**2) / np.sum((y - y.mean())**2))
 
# Plotting
fig, axes = plt.subplots(4, 1, figsize=figsize, gridspec_kw={'height_ratios': [1.5, 1, 1, 1]})
 
# Information for the first visual
x_axis_label = date_column  # Set this to the selected X-axis granularity
title_info = f"Time Series Decomposition (TSD)\n X Axis = {x_axis_label} | R² = {r_squared:.4f}"
 
# Original data
axes[0].set_title(f'{title_info}\n Original Data')
result.observed.plot(ax=axes[0])
axes[0].set_ylabel(data_column)
 
# Trend component
axes[1].set_title('Trend')
result.trend.plot(ax=axes[1])
axes[1].set_ylabel('Trend')
 
# Seasonal component
axes[2].set_title('Seasonality')
result.seasonal.plot(ax=axes[2])
axes[2].set_ylabel('Seasonality')
 
# Residual component
axes[3].set_title('Residuals')
result.resid.plot(ax=axes[3])
axes[3].set_ylabel('Residuals')
 
for ax in axes:
    ax.set_xlabel("")
 
plt.tight_layout()
plt.show()

Autocorrelation Function (ACF) and Partial Autocorrelation Function (PACF)

import pandas as pd
import matplotlib.pyplot as plt
from statsmodels.graphics.tsaplots import plot_acf, plot_pacf
from statsmodels.tsa.seasonal import seasonal_decompose, STL
from statsmodels.tools.sm_exceptions import ValueWarning
import warnings
import numpy as np
import os
 
'''
In Power BI, assume the dataset looks like:
     0 - Date Column
     1 - Data Column
     2 - Selected Seasonality Column
'''
try:
    # If 'dataset' exists, we're likely in the Power BI environment
    df = dataset
except NameError:
    # If dataset does not exist, then run locally
    script_dir = os.getcwd()  # current working directory
    file_name = "Monthly data.csv"
    file_path = os.path.join(script_dir, file_name)
    df = pd.read_csv(file_path)
 
# Identify column names
date_column = df.columns[0]          # e.g., "Monthly" or "Quarterly" date strings
data_column = df.columns[1]          # e.g., "SUM Data"
seasonality_column = df.columns[2]   # e.g., "Selected Seasonality"
 
# Clean Data
df[date_column] = pd.to_datetime(df[date_column]) # Convert date column to datetime
df.sort_values(date_column, inplace=True) # Sort by date just in case
df.set_index(date_column, inplace=True) # Set index to date column
df[data_column] = df[data_column].fillna(df[data_column].mean())
 
# Grab the first "Selected Seasonality" from the dataset
selected_seasonality = df[seasonality_column].iloc[0]
 
# Dynamic Formatting
visual_width = 800  # Default width in pixels
visual_height = 600  # Default height in pixels
try:
    if 'width' in dataset.columns and 'height' in dataset.columns:
        visual_width = dataset['width'].iloc[0]
        visual_height = dataset['height'].iloc[0]
except NameError:
    visual_width = 800
    visual_height = 600
 
# Aspect ratio scaling
aspect_ratio = visual_width / visual_height
base_size = min(visual_width, visual_height) / 100  # Scale based on smaller dimension
figsize = (base_size * aspect_ratio, base_size)
 
# Dynamic font scaling
font_scale = base_size / 8
plt.rcParams.update({'font.size': 10 * font_scale})
 
## ---------------------  ACF ---------------------
 
# Set relevant lags
if selected_seasonality == 365:
    frequency = 'Daily'
    max_lags = min(len(df)-1, 365)  # Last Year
elif selected_seasonality == 7:
    frequency = 'Weekly'
    max_lags = min(len(df)-1, 26)  # ~6 months of weekly lags or up to 26
elif selected_seasonality == 12:
    frequency = 'Monthly'
    max_lags = min(len(df)-1, 12)  # up to 12 lags (1 year of monthly)
elif selected_seasonality == 4:
    frequency = 'Quarterly'
    max_lags = min(len(df)-1, 4)   # up to 4 lags (1 year of quarterly)
elif selected_seasonality == 1:
    frequency = 'Yearly'
    max_lags = 1
else:
    frequency = 'Unknown'
    max_lags = min(len(df)-1, 12)  # fallback
 
# Plot ACF and PACF
if len(df) > 1:
    fig, (ax1, ax2) = plt.subplots(2, 1, figsize=figsize )
    try:
        plot_acf(df[data_column], lags=max_lags, ax=ax1)
        ax1.set_title(f'Autocorrelation Function (ACF)\n Frequency: {frequency}, Lags: {max_lags}')
 
        plot_pacf(df[data_column], lags=max_lags, method='ywm', ax=ax2)
        ax2.set_title(f'Partial Autocorrelation Function (PACF)\n Frequency: {frequency}, Lags: {max_lags}')
    except ValueError as e:
        ax1.set_title(f'ACF Error: {e}')
        ax2.set_title(f'PACF Error: {e}')
else:
    print("Not enough data to calculate ACF or PACF.")
 
plt.tight_layout()
plt.show()

Power BI Environment

A premium license of some kind (Pro, PPU, Premium capacity, or Fabric) is required to display Python visuals in the service. More information: Microsoft — Python Power BI Visuals, Licensing. Once configured, your data is ready to be sliced, filtered, and explored.

Bonus: Generating Test Data in Excel

Here is an Excel formula that creates an upward trend in year one and a downward trend in year two, with embedded quarterly seasonality. Create a daily-grain time series of dates in column A, then use this in column B:

=100 + SIN(RADIANS(MONTH(A2)*30)) * 20 + (RAND() * 10 - 5)

Downloadable Files

Source code and files are on our GitHub repo: Analytical Ants — Time Series Decomposition and Autocorrelation Function in Power BI with Python.

Frequently Asked Questions

What is time series decomposition?

Time series decomposition breaks a series into its key components — trend, seasonality, and residuals — to reveal the underlying patterns and support more accurate forecasting. In Power BI you can compute it inside a Python visual with statsmodels' seasonal_decompose or STL.

How do you make the decomposition seasonality dynamic in Power BI?

Create a field parameter over a calendar table with columns for the first day of year, quarter, month, and week, and a SELECTEDVALUE measure for the seasonal period. Feed the selected seasonality into the Python visual so users can switch grains — daily, weekly, monthly, quarterly, yearly — without new visuals.

What license do Python visuals need in the Power BI service?

Displaying Python visuals in the service requires a premium license of some kind — Pro, Premium Per User, or Premium/Fabric capacity. Local development in Power BI Desktop only needs a working Python environment with pandas, matplotlib, numpy, and statsmodels installed.

Put your data to work

Advanced analytics only pays off when it sits on data you can trust. If you want to bring dynamic forecasting and decomposition to a reporting environment that actually reconciles, let's talk.

About the author — William Rodriguez is the founder of Analytical Ants, the delivery arm of Analytical Solutions. He spent roughly a decade architecting enterprise BI, data science, and data platforms for operations running $10M–$60B in revenue, and holds an MBA from Emory University's Goizueta Business School. More about Analytical Ants.