Time intelligence — YoY, YTD, rolling averages — Power BI Module 6

Finance and management reporting are about as time-heavy as analysis gets. Year-over-year growth, month-to-date, last-twelve-months, fiscal-year vs calendar-year — these are the operating language of finance teams. Power BI handles time intelligence beautifully — IF you give it a properly-structured Date dimension. Without one, you'll write painful workarounds.

Building a Date dimension

text

// In Power Query, create a new query:
let
    StartDate = #date(2018, 1, 1),
    EndDate = #date(2030, 12, 31),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    Dates = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
    ToTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    AddYear = Table.AddColumn(ToTable, "Year", each Date.Year([Date]), Int64.Type),
    AddQuarter = Table.AddColumn(AddYear, "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date])), type text),
    AddMonth = Table.AddColumn(AddQuarter, "Month", each Date.MonthName([Date]), type text),
    AddMonthNo = Table.AddColumn(AddMonth, "Month No", each Date.Month([Date]), Int64.Type)
in
    AddMonthNo

A reusable Date dimension. Load this query, mark the Date table (Modeling tab → Mark as Date Table), and relate your fact tables to it on the date column.

The four most-used time-intelligence functions

text

// Same period last year
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

// Year-to-date
Sales YTD = CALCULATE([Total Sales], DATESYTD('Date'[Date]))

// Rolling 12 months
Sales L12M = CALCULATE([Total Sales],
  DATESBETWEEN('Date'[Date], DATEADD(MAX('Date'[Date]), -12, MONTH), MAX('Date'[Date]))
)

// YoY growth
Sales YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])

These four cover most of finance-team time-intelligence work in Power BI.

Why your time intelligence breaks

Three common reasons. (1) No proper Date dimension — you're using a date column in the fact table. SAMEPERIODLASTYEAR returns nothing. Fix: build a Date table. (2) The Date table isn't marked as a date table (Modeling → Mark as Date Table). Fix: mark it. (3) Gaps in the Date table — e.g., you only have dates where sales occurred. Fix: make the table contiguous from your earliest to latest expected date.

Fiscal vs calendar year

Most time-intelligence functions default to a calendar year (January-December). Fiscal year support is built in: TOTALYTD([Total Sales], 'Date'[Date], "3/31") computes YTD assuming the fiscal year ends March 31. Add a Fiscal Year column to your Date dimension for slicing, but the time-intelligence math itself can stay simple.

Exercise

Build a Power BI report with a fact table, a Date dimension (use the M script above), and these four measures: Total, YTD, Same Period Last Year, YoY %. Drop them all on a line chart with the Date column on the x-axis. Add a Year slicer. Do they all behave as expected when you change the year selection?