Webbo3 Power BI · Cohort 1 · Day 12 of 16

Day 12: Time Intelligence

Year to date, the same period last year, and growth, calculated by Power BI from the calendar instead of years you type in.

On Day 11 you typed 2024 into a measure. It worked until January. Time intelligence measures work out "this year", "last year" and "last month" from whatever dates the reader is looking at.

Open your Day 11 file. Check two things before you start: the Date table is marked as a date table (Table tools, Mark as date table), and it is related to Sales[Date]. Every function today depends on both.

1. What Time Intelligence Is

Definition

Time intelligence functions are DAX functions that change the dates a measure is calculated over: from the start of the year, back one year, back one month. They are used inside CALCULATE, or come with CALCULATE built in.

Real-Life Example

Your bank app shows "spent this month" and "spent last month" side by side. You never tell it which month is last month; it works it out from today. Time intelligence does that for a report: it works out "last year" from whichever dates are on the row.

Why It Needs the Date Table

The functions walk along a calendar: back 365 days, forward to the end of the month. That only works on a table with every date, no gaps and no repeats, which is exactly the Date table from Day 7. Sales [Date] has repeats (many sales a day) and may have gaps (a day with no sales), so always point time intelligence at 'Date'[Date].

Types

2. Year to Date: TOTALYTD

Definition

Year to date (YTD) is the running total from 1 January to the end of the period you are looking at. On the March row it is January plus February plus March.

Real-Life Example

A savings target for the year: by March you want to know not what you saved in March, but everything saved since New Year.

1
2
3
4
5
6

// Running total from 1 January, restarting every year
Revenue YTD =
TOTALYTD (
    [Total Revenue],   // what to add up
    'Date'[Date]       // the calendar to walk along
)

December's YTD equals the year's total, and so does the total row: the "year to date" of the whole year is the whole year. Put 2023 on the page as well and the YTD column restarts at January 2023's figure.

3. Last Year: SAMEPERIODLASTYEAR

Definition

SAMEPERIODLASTYEAR ( 'Date'[Date] ) takes the dates the row is looking at and moves every one of them back a year. Inside CALCULATE, it gives the measure for the same period last year.

Real-Life Example

A shop comparing this December with last December, not with November. December is always busy, so only last December tells you whether this one was good.

1
2
3
4
5
6
7
8
9
10

// The same months, one year earlier
Revenue LY =
CALCULATE (
    [Total Revenue],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

// Growth against last year; blank when there is no last year
Revenue YoY % =
DIVIDE ( [Total Revenue] - [Revenue LY], [Revenue LY] )

Feb grew the most and Sep the least. Look at the 2023 petrol price from Day 9: it jumped in June 2023, so from June the comparison is against the new price, and every month from June grows less than every month before it. Time intelligence gave you the numbers; the business gave you the reason.

The Blank Is Correct

The data starts in January 2023, so there is no 2022 to compare with. Revenue LY is blank, and DIVIDE turns the growth into blank instead of an error. A blank here is the honest answer, not a broken measure.

4. Last Month: DATEADD

Definition

DATEADD ( 'Date'[Date], -1, MONTH ) moves the row's dates by a number of units: here minus one month. The units can be DAY, MONTH, QUARTER or YEAR. Minus goes back, plus goes forward.

Real-Life Example

The bank app's "compared with last month". Useful for spotting a sudden drop quickly, before a whole year has passed.

1
2
3
4
5
6
7
8
9
10

// The previous month's revenue
Revenue PM =
CALCULATE (
    [Total Revenue],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

// Change against the previous month
Revenue MoM % =
DIVIDE ( [Total Revenue] - [Revenue PM], [Revenue PM] )

January's previous month is December 2023: DATEADD crosses the year without being told. Month on month jumps about a lot, because busy and quiet months follow each other. That is why managers look at year on year for the trend and month on month for alarms.

5. Common Mistakes

• Using Sales[Date] in the function. Results come out wrong or blank in ways that are hard to spot. Always 'Date'[Date].
• The Date table is not marked. Some functions still work, some do not. Mark it once and stop wondering.
• Month on the rows comes from Sales. Rows must come from the Date table too, or the time functions have no dates to move.
• Reading a blank as a zero. Blank means "nothing to compare with", not "no sales".

6. Putting It Together

The regional manager wants one picture: is 2024 running ahead of 2023, month by month? A line chart with Date[Month] on the X-axis and both Total Revenue and Revenue LY as values, filtered to 2024, draws this year against last year.

2024 is above 2023 in every month. Year to date at December: ₦88,797,696 against ₦57,056,529, growth of +55.6%.

1
2
3
4
5
6

// Year to date, last year: TOTALYTD over last year's dates
Revenue YTD LY =
CALCULATE (
    [Revenue YTD],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Measures build on measures here too: Revenue YTD LY is Revenue YTD with the dates moved back a year.

Do This Now

1. Check the Date table is marked and related.
2. Write Revenue YTD, Revenue LY, Revenue YoY %, Revenue PM and Revenue MoM %, and format them.
3. Build the 2024 matrix and check every number against this lesson.
4. Select 2023 and explain the blanks to yourself.
5. Build the this-year-against-last-year line chart.
6. Do the Try This, then carry on with the Solo Project.

Checkpoint

Next, Day 13: building visuals. Which chart answers which question, and how to make each one readable. Assignment 3 is released.