Start Learning Contact Us
Power BI Mastery

Time Intelligence in DAX

Lesson 6 of 10

Comparisons over time are the most requested numbers in any report: this year against last year, the running total for the year, the average of the last three months. DAX has a family of functions for them.

The requirement: a date table

Time intelligence functions need a date table that:

  • has one row for every day, with no gaps,
  • covers every date in your data,
  • is marked as a date table,
  • is related to the fact table.

The modelling lesson showed how to build one. Always use the date table’s column in these functions, and its columns in your visuals, not the date column from the fact table.

Year to date

Sales YTD =
TOTALYTD ( [Total Sales], 'Date'[Date] )

In a visual by month, each month shows the total from 1 January to the end of that month. TOTALQTD and TOTALMTD do the same for quarters and months.

For a financial year that ends on 31 March, add the year-end date:

Sales FYTD =
TOTALYTD ( [Total Sales], 'Date'[Date], "3-31" )

Same period last year

Sales LY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

SAMEPERIODLASTYEAR shifts the dates in the current filter context back by one year.

Year-over-year change

Sales YoY % =
VAR Current = [Total Sales]
VAR Previous = [Sales LY]
RETURN
    DIVIDE ( Current - Previous, Previous )

DIVIDE returns a blank when there is no previous-year value, so the first year in your data shows blank, not an error. Format the measure as a percentage.

Shifting by any period

DATEADD moves the dates by a number of days, months, quarters or years:

Sales Previous Month =
CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )

Rolling periods

DATESINPERIOD returns a range of dates ending at a given date:

Sales Last 3 Months =
CALCULATE (
    [Total Sales],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)

Sales 3M Average =
DIVIDE ( [Sales Last 3 Months], 3 )

A rolling average smooths month-to-month noise and makes a trend easier to see.

Running total across all time

Sales Running Total =
CALCULATE (
    [Total Sales],
    FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

This removes the date filter and then keeps every date up to the last one in the current context.

Common problems

  • Blank results. The date table does not cover the period, or is not marked as a date table.
  • Wrong totals for partial years. Comparing a year to date with a full previous year. Compare like with like.
  • Using the fact table’s date column. It usually has gaps, which breaks these functions.

Practice

Create measures for sales year to date, sales in the same period last year, and the percentage change between them. Show all three in a matrix by month.