Power BI time intelligence: compare this period with the last
Power BI time intelligence is the set of DAX functions that calculate figures over periods, such as year to date, the same month last year or a rolling quarter. Nearly every business report needs these comparisons. However, the functions only return correct results when the model has a proper date table. So this guide covers the setup first, then the functions and the errors people hit.
- Year to date totals
- Prior period compare
- Needs a date table
Last updated
What is Power BI time intelligence?
Business questions are usually about change. Are sales up on last year? How does this quarter compare with the one before? Time intelligence functions answer these by shifting or extending the dates a measure looks at.
For example, a year to date function takes the current date filter and widens it back to the start of the year. A prior year function moves the filter back twelve months. The base measure then runs over that new set of dates.
Microsoft's DAX reference groups these functions in their own category. It also states a condition up front: mark a table with a date column as the date table before you use them.
Common time intelligence functions in DAX
A handful of functions cover most reporting needs, so learn these before the rarer ones.
| Function | What it returns | Typical use |
|---|---|---|
| TOTALYTD | A measure from the start of the year to date | Running annual totals |
| DATESYTD | The set of dates from the start of the year | Custom year to date logic |
| SAMEPERIODLASTYEAR | The same dates one year earlier | Year-over-year comparison |
| DATEADD | Dates shifted by a chosen interval | Prior month or prior quarter |
| DATESINPERIOD | A span of dates ending at a given date | Rolling periods |
| PREVIOUSMONTH | All dates in the month before | Month-over-month change |
Why does the date table matter so much?
The functions work by moving through a column of dates. If that column has gaps, a shifted period can land on a date that does not exist. As a result, the measure returns a blank or a wrong figure.
So the model needs a dedicated date table with one row for every day, related to each fact table. The measures then use the date column from that table, not the date on the fact rows.
Desktop also offers an auto date/time option, which creates hidden date tables for each date column. It helps beginners, but Microsoft's guidance explains its limits for larger models. Therefore most teams switch it off and build one shared table.
How to set up Power BI time intelligence
Four steps take a model from raw dates to reliable period measures.
Add a date table
First, create a table with every date in the range you report on. Include columns for year, quarter and month.
Mark and relate it
Then mark it as the date table and relate it to the date key in each fact table.
Write the base measure
After that, write a plain measure such as total sales. Every period measure will build on this one.
Add the period measures
Finally, wrap the base measure with functions such as TOTALYTD or SAMEPERIODLASTYEAR. Check each result against a figure you know.
What goes wrong with period calculations?
The first error is using the fact table's date in the function. It seems to work until a day has no sales. Then the comparison breaks, because that date is missing from the column.
Fiscal years cause the second. Many functions assume a calendar year unless you pass a year-end date. So a company with a July year start must state that, or build custom logic.
Weeks are the third. The classic functions have no notion of weeks. Microsoft has since introduced calendar-based time intelligence, which adds week functions and custom calendars. Since that feature is newer, read the current documentation before you depend on it.
Power BI time intelligence: common questions
Does Power BI time intelligence need a date table?
Yes, for reliable results. The DAX reference tells you to mark a date table before using these functions. That table needs a continuous run of dates with no gaps. Without it, shifted periods can miss days and return wrong numbers. So build the table first, and then write the period measures.
How do I handle a fiscal year?
Several functions accept a year-end date, so you can pass the last day of your fiscal year. For example, the year to date functions take that optional argument. Also add fiscal year and fiscal period columns to the date table. Then visuals can group by them, and readers see labels that match the finance calendar.
Should I keep auto date/time switched on?
It depends on the model. The option adds a hidden date table for every date column, which suits quick personal reports. However, those tables add size, and they cannot be shared across fact tables. Microsoft's guidance discusses this trade-off. For a shared model, most teams turn the option off and use one date table.
Can Power BI time intelligence compare weeks?
The classic functions work in days, months, quarters and years, so weeks needed custom DAX. Microsoft has added calendar-based time intelligence with week functions. Because it is a newer feature, check its status and requirements on Microsoft Learn. A week number column in your date table also remains a simple, solid base.
Can you build our period measures for us?
Yes. Power BI Agency builds date tables, base measures and period comparisons, and tests each against your finance figures. We are a division of Sataware Technologies and independent of Microsoft. The work runs online inside your own tenant, with weekly demos. A free 30-minute assessment is the first step.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.