Applications of window functions

Time Series Analysis in Power BI

Kevin Barlow

Data Analytics Professional

Context and importance

Expanding and Rolling window functions can be applied in many different ways!

There are several calculations you can apply to specific time periods:

- RANK()
- LOOKUPVALUE()
- CHISQ.INV()
- GEOMEAN()

SAMEPERIODLASTYEAR vs. PARALLELPERIOD

SAMEPERIODLASTYEAR()

Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.

SAMEPERIODLASTYEAR(<dates>)

PARALLELPERIOD()

Returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.

PARALLELPERIOD(<dates>,
    <number_of_intervals>,
    <interval>)

¹ https://learn.microsoft.com/dax

Analyzing the same window as last year

We may want to see how our exact same window looked last year.

Applies a window function to older data.
Allows us to get context at what the same calculation was.
Quickly process calculations across various points in time.

Typical steps to this kind of analysis:

Calculate an important measure or KPI for the current year.
Apply the same calculation to other similar time periods.
Calculate the difference between these values to understand the amount of change.

Analyzing the same window as last year examples

In industry, these kinds of analyses are very common. They provide two very key data points:

How is our organization performing on a particular KPI in the context of the current year?

How are we doing in the context of the same period of time and KPI from last year? Are we improving?

Avg Cost = CALCULATE(
    AVERAGE(stores[cost]),
    stores[date] >= 
        DATEADD(TODAY(), -30, DAY))

LY Avg Cost = CALCULATE([Avg Cost],
    SAMEPERIODLASTYEAR(stores[date]))

Calculating year over year change

We can calculate exactly how our data has changed from last year by applying a window to historical data.

Known as a Year-over-Year (YoY) calculation
Provides a sense of progress compared to history
Typically shown as a percentage of change

# Assuming current month is February

CY Jan Revenue = CALCULATE(
    SUM(sales[revenue]),
    PREVIOUSMONTH(sales[date]))

LY Jan Revenue = CALCULATE(
    [CY Jan Revenue],
    SAMEPERIODLASTYEAR(sales[date]))

Jan Revenue YoY = (
    ([CY Jan Revenue]-[LY Jan Revenue])
        / [LY Jan Revenue])

Let's practice!

Time Series Analysis in Power BI