Power Platform DAX Time Intelligence

Leaders ask time-based questions all day. How did sales compare with last year? What is the total so far this quarter? DAX time intelligence functions answer these questions with short, readable formulas.

The Date Table Requirement

Time intelligence works only with a proper date table. The table needs one row for every day in the range of your data, with no gaps. Build one with a DAX formula.

Calendar = CALENDAR(DATE(2022,1,1), DATE(2026,12,31))

Add columns for Year, Month Number, Month Name, and Quarter. Mark the table as a date table through Table tools, then choose the date column. Link it to the Date column of your fact table with a one-to-many relationship.

 Calendar (dates)               Sales (facts)
| Date | Year | Month |  1 --> * | OrderDate | Amount |

Base Measure First

Every time calculation builds on a simple measure.

Total Sales = SUM(Sales[Amount])

Year, Quarter, and Month to Date

Sales YTD = TOTALYTD([Total Sales], 'Calendar'[Date])
Sales QTD = TOTALQTD([Total Sales], 'Calendar'[Date])
Sales MTD = TOTALMTD([Total Sales], 'Calendar'[Date])

Each function accumulates values from the start of the period up to the current date in the chart.

Comparing With a Previous Period

Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
Sales Prev Month = CALCULATE([Total Sales], DATEADD('Calendar'[Date], -1, MONTH))

SAMEPERIODLASTYEAR shifts the date filter back one year. DATEADD shifts by any number of days, months, quarters, or years.

Growth Percentage

YoY Growth % =
DIVIDE([Total Sales] - [Sales Last Year], [Sales Last Year])

DIVIDE handles a zero denominator gracefully and returns blank instead of an error.

Rolling Periods

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

Rolling totals smooth out spikes and reveal the underlying trend.

Diagram: How the Filter Shifts

Selected month: March 2026
        |
        |  SAMEPERIODLASTYEAR
        v
Filter moves to: March 2025
        |
        v
SUM of Amount for March 2025  --> shown as "Sales Last Year"

Timeline:
2025: Jan  Feb [MAR] Apr ...
2026: Jan  Feb [MAR] Apr ...
                ^ current column compares with the one above

Layman Example: A Fitness Tracker

A fitness band reports steps today, steps this week, and steps compared with last week. The band never asks you to count manually. It remembers a calendar of every day and slides a window across that calendar. A date table gives Power BI the same memory, and time intelligence functions slide the window.

Typical Mistakes

• Using the date column of the fact table instead of the date table in the visual axis.
• Leaving gaps in the date table, which breaks the functions silently.
• Forgetting to mark the date table.
• Mixing fiscal and calendar years without setting the year-end date in the function.

Fiscal Years

Many organizations close their books in March. Pass the year-end date as a third argument to TOTALYTD.

Sales Fiscal YTD = TOTALYTD([Total Sales], 'Calendar'[Date], "3/31")

Testing Your Measures

Verify every time measure against a figure you can check manually. Place the measure in a table next to month names and compare the numbers with an Excel pivot. Test the first and last month of the data, because year-to-date and prior-year measures often show blanks at the edges. Confirm the results before sharing the report with leaders.

Reading Results in a Visual

Place Year and Month from the date table on the axis of a line chart. Add Total Sales, Sales Last Year, and Sales YTD as values. The chart draws three lines that tell a complete story: the current performance, the same period a year earlier, and the running total. Add a card visual for growth percentage and format it with a green or red indicator so readers grasp the result at once. Keep the date table fields on the axis and never use the raw order date from the fact table, since only the date table carries the full calendar that time functions need.

Key Takeaways

A complete date table plus a handful of functions covers year-to-date, prior period, growth, and rolling totals. Build the base measure first, and each time calculation takes one short formula.