Window functions in Power BI

Time Series Analysis in Power BI

Kevin Barlow

Data Analytics Professional

Context and importance

The identified span of a time series datasets impacts the kind of analysis we can perform. Different sizes of time "windows" carry different levels of information.

Difference in Time Spans

Expanding window functions - how it works

Have a single anchor point
Can "point" forward or backward

Expanding Window Functions

Expanding window functions - use cases

Expanding window functions generally apply towards larger scale analyses from a reference point.

Some examples include:

Adding up all the profit we have made since a key decision was made.
Calculating the average temperature for a given location over all data until today.
Counting the number of products shipped so far this year.

Examples:

CALCULATE(SUM(sales[profit]), 
    sales[date] >= DATE(2020,1,1))

CALCULATE(AVERAGE(weather[temp]),
    weather[date] <= TODAY())

TOTALYTD(COUNT(shipping[id]), 
    shipping[ship_date])

Rolling window functions - how it works

Have relative anchor points
Apply to different time spans as new data arises

Rolling Window Functions

Rolling window functions - use cases

Rolling window functions are typically used to analyze the current state. Quite often, these are used to calculate various KPIs that are important for the health of an organization.

Some examples include:

The highest price a stock as reached in the last thirty days.
The average discount provided to a customer over the last year.
Calculating the expected shipping delay over the last six months.

Examples:

CALCULATE(MAX(stocks[high]), 
    stocks[date] >= DATEADD(TODAY(), 
    -30, DAYS))

CALCULATE(AVERAGE(sales[discount]), 
    sales[date] >= DATEADD(TODAY(), 
    -1, YEAR))

CALCULATE(AVERAGE(ship[delay]), 
    ship[ship.date] >= DATEADD(TODAY(), 
    -6, MONTH))

Let's practice!

Time Series Analysis in Power BI