Chapter 6 · 9 levels
Time intelligence
The Date table and why it must be continuous and marked, then year-to-date and month-to-date totals, last year's figures with SAMEPERIODLASTYEAR, previous month with DATEADD, and year-over-year and month-over-month growth.
Start“How are we doing compared with last year?” is the question every manager asks first, and the one a spreadsheet answers worst. In Power BI it takes one measure, as long as the model has a proper calendar.
This chapter starts with that calendar: the 'Date' table, one row per day from 2023 to 2025, with
no gaps, related to the orders and marked as the date table. You add a column to it, then build the
measures every sales report carries: year-to-date and month-to-date running totals, last year’s
figure for the same period, the previous month, and growth rates year over year and month over
month, written with variables and DIVIDE. One level shows what breaks when the calendar isn’t
marked, so you can recognise the symptom in someone else’s report.
The matrix and the DAX engine are a practice tool built for this site, and each lesson gives the exact place to do the same in Power BI Desktop. The chapter ends with a boss level: a year-over-year report for the app channel.
Levels
- A weekend column on the Date table
from Tyler Reed, Customer Service Manager
date table, calculated column, mark as date table, weekday, calendar
- Year-to-date sales with TOTALYTD
from Laura Bennett, Finance Manager
totalytd, year to date, running total, date table, time intelligence
- Month-to-date by day with DATESMTD
from Chris Morgan, Promotions Lead
datesmtd, totalmtd, month to date, calculate, time intelligence
- Last year's sales with SAMEPERIODLASTYEAR
from Nicole Hayes, Merchandise Planner
sameperiodlastyear, prior year, calculate, date table, time intelligence
- Year-over-year growth with VAR and DIVIDE
from Rachel Kim, Category Manager
yoy, var, divide, sameperiodlastyear, growth rate
- Previous month and MoM % with DATEADD
from Olivia Grant, E-commerce Manager
dateadd, previous month, mom, divide, time intelligence
- Why is my YTD just the month?
from Steven Clark, Regional Operations Director
mark as date table, totalytd, date table, filter context, troubleshooting
- Year-to-date last year
from Laura Bennett, Finance Manager
datesytd, sameperiodlastyear, ytd last year, calculate, time intelligence
- Boss: the app channel's year-over-year report
from Andrew Wallace, Head of Mobile
sameperiodlastyear, yoy, datesytd, totalytd, var