Date Tables and Time Intelligence: YTD, Prior Year and Growth | Free Power BI Course: From Beginner to Advanced

What you'll learn

Build a calendar table with DAX and mark it as a date table.
Calculate year-to-date (YTD), prior year and year-over-year growth.
Compare with the prior month and calculate moving averages.
Avoid the classic mistakes when comparing partial periods.
Read the real trend in Summit's sales for 2025 and 2026.

"Where are we for the year?", "How are we doing against last year?", "Did we grow over last month?" Those questions come up in every results meeting. DAX has time intelligence functions that answer each one in a single line, but they need one thing first: a proper date table. This lesson wraps up the Intermediate level.

730dates with no gaps in Summit's calendar: every day of 2025 and 2026

+8.3%sales growth in 2026 vs. 2025 ($268,259 vs. $247,710)

6time measures cover almost every meeting: YTD, prior year, prior-year YTD, two growth rates and prior month

Why you need a calendar table

Time intelligence functions need a table with every date in a period, with no gaps (even days without sales), related to the date columns in your fact tables. A calendar table of your own also lets you:

Filter Sales and Targets with the same date slicer (both relate to the calendar).
Add useful columns: year, quarter, month, month name, week, fiscal year, business day.
Sort months correctly (January, February… not alphabetically).

Turn off Auto date/time

By default Power BI creates a hidden date table for every date column. Once you have your own calendar, turn it off under File › Options and settings › Options › Current File › Data Load › Auto date/time. The model gets smaller and less confusing.

Build the calendar with DAX

Go to Modeling › New table and type:

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "Year-Month", FORMAT ( [Date], "yyyy-mm" ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Weekday Number", WEEKDAY ( [Date], 2 ),
    "Is Weekend", WEEKDAY ( [Date], 2 ) >= 6
)

CALENDAR returns a one-column table named Date with every date between the two you give it. Choose a range that covers all your data (you can derive it from MIN and MAX of Sales[OrderDate], or use CALENDARAUTO()).
ADDCOLUMNS adds calculated columns to that table.
WEEKDAY([Date], 2) returns 1 for Monday and 7 for Sunday.
Because CALENDAR is also a function name, Power BI writes the table as 'Calendar' with quotes in formulas. We'll do the same.

You can also build the calendar in Power Query or import it from Excel. What matters is that it contains every date and the columns you need.

Three required steps

Table toolsMark as date tableSelect Calendar, choose Mark as date table and pick the Date column.
Model viewRelate it'Calendar'[Date] to Sales[OrderDate] (1:*), and to the date of any other fact table: at Summit, also Targets[Date] (the first of each month, Lesson 2).
Column toolsSort the month by its numberSelect the Month column and choose Sort by column › Month Number. Otherwise months sort alphabetically.

From now on, always use the Calendar columns (Year, Month) in visuals and slicers, not the fact table's date.

1. Your chart shows the months as "Apr, Aug, Dec, Feb…". What do you do?

Rename the months with numbers in front.
Sort the Month column by the Month Number column.
Change the chart type.

"Sort by column" tells Power BI to use the month number to order the month name.

The time intelligence measures

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

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

YoY % =
DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )

Sales YTD PY =
CALCULATE ( [Sales YTD], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

YTD YoY % =
DIVIDE ( [Sales YTD] - [Sales YTD PY], [Sales YTD PY] )

Sales PM =
CALCULATE ( [Total Sales], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

They all use CALCULATE (Lesson 7) to change the date context: SAMEPERIODLASTYEAR shifts the filtered dates back one year, and DATEADD shifts them by however many periods you specify.

The results, with numbers

Summit's sales from the course file (in dollars, rounded), with the months of 2026 on rows:

Month	Sales	Sales YTD	Sales PY	YoY %	Sales YTD PY	YTD YoY %
Jan	23,780	23,780	21,989	8.1%	21,989	8.1%
Feb	20,108	43,889	19,547	2.9%	41,536	5.7%
Mar	21,476	65,365	17,087	25.7%	58,623	11.5%
Apr	20,976	86,341	16,549	26.8%	75,172	14.9%

How to read it: April grew 26.8% over April 2025, and year to date Summit is up 14.9%. Compared with the prior month, April dipped 2.3% (20,976 vs. 21,476), but that may just be seasonality, which is why comparing with the same month last year is usually fairer.

2. Using the table above, what's the change from February to March 2026?

6.8%
25.7%
11.5%

(21,476 − 20,108) ÷ 20,108 = 0.068 = 6.8%. The 25.7% is the change vs. March 2025, and 11.5% is the year-to-date change.

What Summit's full series tells you

The table only shows four months. Put Month on the axis, Total Sales as the value and Year in the legend of a line chart, and you'll see all 24 months:

2025 $247,710Lowest month: September ($15,771).
2026 $268,259Highest month: July ($29,913).
Year-over-year growth +8.3%2026 beats 2025 in 9 of 12 months.

Total Sales by month and year from the course file. Month-to-month swings are large, which is why you should also look at YTD and the moving average.

Three insights only time intelligence makes obvious:

Single months mislead: August 2026 fell 15.3% vs. August 2025 and October fell 19.0%, yet September grew 38.5%. Without YTD, every monthly meeting would be a roller coaster.
YTD growth is cooling: YTD YoY % peaked at 16.7% in May and closed the year at 8.3%. The second half grew less than the first, a warning worth raising with the sales team.
The moving average smooths out noise: April 2026 sales were $20,976, but the three-month trend is $20,854. Use the moving average to talk about trends and single months to talk about specific events.

Special fiscal calendars

The functions in this lesson assume calendar months. If your company uses a 4-4-5 week retail calendar, you'll need comparisons built on your own calendar columns (period number, week number). Microsoft introduced calendar-based time intelligence for these cases in 2025; check the official documentation to see whether it's generally available in your version.

Moving averages and custom periods

A 3-month moving average smooths out the ups and downs and shows the trend:

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

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

For April 2026: ($20,108.23 + $21,476.37 + $20,976.44) ÷ 3 = $20,853.68. DATESINPERIOD takes the dates from the last date in context back by the number of periods you specify.

Fiscal year

If your fiscal year ends on a different date (June 30 is common; the federal government uses September 30), pass it to TOTALYTD: TOTALYTD([Total Sales], 'Calendar'[Date], "6/30"). Add a "Fiscal Year" column to the calendar as well.

The partial-period trap

Don't compare half a period with a full one

On May 15, 2026, "Sales YTD" includes data through May 15, but if you filter the whole year 2026, "Sales YTD PY" uses December 31 as its reference and adds up all of 2025. The result: a fake decline. A common fix is to flag which calendar dates have data and use only those in the comparison.

Add a calculated column to the Calendar table:

Has Sales = 'Calendar'[Date] <= MAX ( Sales[OrderDate] )

And create the comparable measure:

Sales YTD PY Comparable =
CALCULATE (
    [Sales YTD],
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] ),
        'Calendar'[Has Sales] = TRUE ()
    )
)

CALCULATETABLE keeps only the dates in context that have data (say, January 1 to May 15, 2026), SAMEPERIODLASTYEAR moves them to 2025, and the YTD is calculated only through May 15, 2025. Now you're comparing the same stretch of both years.

3. What's essential for TOTALYTD and SAMEPERIODLASTYEAR to work properly?

A sales table with one row per day.
A continuous date table, marked as a date table and related to the facts.
Dates stored as text.

Time intelligence functions work on a gap-free date column that covers the whole period.

4. In August 2026, Summit shows YoY % = −15.3% and YTD YoY % = +11.3%. How do you read that?

It's an error: both changes should have the same sign.
2026 will end with a 15.3% decline.
August was worse than August 2025, but January through August is still up.

YoY % compares only the month ($19,001 vs. $22,433); YTD YoY % compares January–August ($182,354 vs. $163,775). One bad month doesn't erase seven good ones, but it does shrink the year-to-date growth.

Common date mistakes

Using the Sales date column in visuals and slicers.

Always use Calendar columns so you filter sales and targets together.

Building the calendar from only the dates that have sales.

CALENDAR with every date, from January of the first year to December of the last.

Forgetting to mark it as a date table.

Mark it so time functions and Date filters behave as expected.

Comparing a partial current year with a full prior year.

Limit the comparison to dates with data, as in this lesson's comparable measure.

Leaving Auto date/time on when you have your own calendar.

Turn it off in the file options: a lighter, clearer model.

Practice exercise: the comparison report

Create the Calendar table, mark it as a date table, relate it and sort the month by its number.
Create Sales YTD, Sales PY, YoY %, Sales PM and 3M Moving Avg.
Build a matrix with Year-Month on rows and these measures as values, with conditional formatting on the growth rates (green if positive, red if negative).
Build a combo chart: this year's sales as columns and the prior year as a line.

See solution

If Sales PY comes back blank, check that the relationship uses 'Calendar'[Date] and that the calendar covers the full prior year.
Conditional formatting for the growth rate: in the matrix, click the measure › Conditional formatting › Font color › Rules: green if the value is ≥ 0, red if it's < 0. Add an icon too so you don't rely on color alone.
For the combo chart, use the line and clustered column chart with 'Calendar'[Month] on the X-axis, Total Sales as the column value and Sales PY as the line value; filter Year = 2026.
Check your work: March 2026 should show Sales $21,476, Sales PY $17,087, YoY % 25.7% and 3M Moving Avg $21,788.
If the YoY % totals look odd, remember they're recalculated from the totals, not by averaging the rows (Lesson 6).

Summary

Build a continuous calendar table, mark it as a date table and relate it to every fact table.
Sort the month name by its number and use Calendar columns in visuals.
TOTALYTD, SAMEPERIODLASTYEAR, DATEADD and DATESINPERIOD handle YTD, comparisons and moving averages.
Calculate growth with DIVIDE and be careful with partial-period comparisons.
Read the month, YTD and the moving average together: at Summit, an August at −15.3% sits alongside a YTD at +11.3%.

You've completed the Intermediate level!

Next lesson: Interactive Reports: Drillthrough, Tooltips, Bookmarks and Parameters. The Advanced level starts with reports users explore with clicks, without crowding the page with charts.