Your business in 360° Time intelligence in DAX: YTD, prior year and period comparisons
Time intelligence in DAX is a group of functions that calculate metrics by period in a Power BI model. With them you get the year-to-date total, the result for the same period last year, a comparison with the previous month or a three-month average. For these functions to work, the model needs a properly built and marked date table. Below you will find how to create one, which functions to use and how to apply them to revenue, expense and profit analysis.
In brief
- Time intelligence functions let you calculate and compare metrics by day, month, quarter and year.
- Without a separate date table that contains every date with no gaps and is related to the fact table, the functions return blank or incorrect values.
- TOTALYTD calculates the year-to-date total, SAMEPERIODLASTYEAR shifts the period back by exactly one year, and DATEADD shifts it by any interval.
- Microsoft now recommends calendar-based time intelligence: it is more flexible and works with fiscal and retail calendars.
- Ready-built Power BI packages for Rivilė and Finvalda users show revenue, expenses and profit without manual report updates.
Contents
- What time intelligence is and why you need it
- The date table: without it the functions will not work
- TOTALYTD, SAMEPERIODLASTYEAR and DATEADD: how they differ
- Measures step by step
- Revenue, expenses and profit by period
- The most common mistakes and how to find them
- Classic or calendar-based time intelligence
- Our view
- Ready-built Power BI packages for Rivilė and Finvalda users
- FAQ
- Sources
What time intelligence is and why you need it
DAX (Data Analysis Expressions) is the formula language used to build measures and calculations in a Power BI model. A simple sum answers the question “how much did we sell?”. Time intelligence answers the questions that matter more to management: how much have we sold since the start of the year, is this month better than last, and how do we compare with the same period last year?
According to Microsoft’s documentation, DAX time intelligence functions let you manipulate data by day, month, quarter and year, and then build and compare calculations across those periods. Microsoft’s training module stresses that these functions are designed for analysing trends, growth and performance over time.
In practice, time intelligence is most often used in:
- KPI cards: year-to-date revenue, monthly profit margin.
- Trend charts: the current year and the previous year on the same axis.
- Month-on-month comparisons: this month, last month, percentage change.
- Management reports: quarterly results, rolling 3- or 12-month averages.
The date table: without it the functions will not work
The most common reason time intelligence “doesn’t work” is not the formula but the model. Microsoft’s design guidance for date tables sets out clear requirements:
- The date column must be of the date (or date/time) data type.
- Values must be unique, with no blank (BLANK) values.
- No date may be missing, not even weekends and public holidays when there were no sales.
- The table must cover full years: from 1 January to 31 December, or full fiscal years.
- With classic time intelligence, the table must be marked as a date table (Mark as Date Table).
It is also important to note that the date column in the fact table, such as the invoice date from Rivilė or Finvalda, is not suitable for use as a calendar. It lacks the days on which there were no transactions, so period shifts become inaccurate. Instead, you create a separate table and relate the fact table’s dates to it.
The simplest date table in DAX:
Kalendorius =
ADDCOLUMNS (
CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2026, 12, 31 ) ),
"Metai", YEAR ( [Date] ),
"Mėnuo", MONTH ( [Date] ),
"Ketvirtis", "K" & QUARTER ( [Date] ),
"Metai-mėnuo", FORMAT ( [Date], "YYYY-MM" )
)Once the table is created, mark it as a date table, specify the date column and create a one-to-many relationship between the calendar and the sales or general ledger table. If your data comes from several sources, such as the accounting system and Excel files, it is worth sorting out how they are combined first: we cover this in more detail in the article Combining Rivilė, Excel and Power BI data.
TOTALYTD, SAMEPERIODLASTYEAR and DATEADD: how they differ
These three functions form the basis of most financial reports. They are described in Microsoft’s list of DAX functions.
| Function | What it returns | When to use it |
|---|---|---|
| TOTALYTD | The value of an expression from the start of the year up to the current date in context | Year-to-date revenue, expenses or profit |
| SAMEPERIODLASTYEAR | A table of dates shifted back by exactly one year | Comparison with the same period last year |
| DATEADD | A table of dates shifted forwards or backwards by a chosen number of intervals | Previous month, previous quarter, two years ago |
| DATESYTD | A table of dates from the start of the year | When you want to use YTD logic inside CALCULATE with additional filters |
| DATESINPERIOD | A range of dates from a given date | Rolling 3- or 12-month metrics |
A simple rule: if all you need is “the same time last year”, SAMEPERIODLASTYEAR is shorter and clearer. If you need flexibility, choose DATEADD.
Measures step by step
The measures below suit a sales table that has an amount column and a date related to the calendar.
- Base measure. Always start with a simple measure that you will reuse in other calculations:
Pajamos = SUM ( Pardavimai[Suma] )- Year-to-date revenue. The TOTALYTD example in Microsoft’s PL-300 hands-on lab shows that the optional third argument specifies the last day of the year, for example “6-30” when the fiscal year ends on 30 June. If your fiscal year matches the calendar year, the argument is not needed:
Pajamos YTD = TOTALYTD ( [Pajamos], 'Kalendorius'[Date] )- Same period last year:
Pajamos PM = CALCULATE ( [Pajamos], SAMEPERIODLASTYEAR ( 'Kalendorius'[Date] ) )- Percentage change. DIVIDE protects against division by zero when there is no data for the previous year:
Pajamų pokytis % =
VAR Dabar = [Pajamos]
VAR Pernai = [Pajamos PM]
RETURN DIVIDE ( Dabar - Pernai, Pernai )- Previous month and rolling 3 months:
Pajamos praėjusį mėn. =
CALCULATE ( [Pajamos], DATEADD ( 'Kalendorius'[Date], -1, MONTH ) )
Pajamos 3 mėn. =
CALCULATE (
[Pajamos],
DATESINPERIOD ( 'Kalendorius'[Date], MAX ( 'Kalendorius'[Date] ), -3, MONTH )
)Measures built this way work at any level of a visual: if you choose quarters in a chart, SAMEPERIODLASTYEAR will automatically return the same quarter of the previous year.
Revenue, expenses and profit by period
The same principle applies beyond sales. A finance team usually needs three groups of metrics:
- Revenue: monthly, quarterly and YTD totals, and comparison with the previous year.
- Expenses: YTD expenses by account group, month-on-month change, and a rolling average that smooths out one-off costs.
- Profit: the difference between the revenue and expense measures, YTD profit and margin.
A practical tip: build the profit measure from existing measures, for example Pelnas YTD = [Pajamos YTD] - [Sąnaudos YTD]. That way every figure in the report rests on the same logic and they all reconcile with one another.
Time intelligence is also the foundation for other analyses. It is used to compare plan with actual in a budget comparison report, to assess future trends in sales forecasting, and to monitor liquidity in cash flow analysis. If you work with Finvalda, the article on the Finvalda profit and loss report will help you understand how profit metrics are structured.
The most common mistakes and how to find them
When a measure returns a blank or the figures do not match the accounting system, check in this order:
- The table is not marked as a date table. Classic functions such as SAMEPERIODLASTYEAR then often return BLANK.
- There is no relationship between the calendar and the fact table, or the relationship uses the wrong column.
- The date is stored as text or a number. Check the column’s data type in Power Query and in the model view.
- The date includes a time component. If the date in the fact table carries hours and minutes, the relationship with a day-level calendar will not work accurately. Microsoft requires day-level granularity with no time portion.
- The calendar is missing dates or has incomplete years. YTD and prior-year totals are then cut short.
- Future dates show amounts. The calendar covers whole years, so “empty” future months may appear in charts. Hide them with a filter or an extra column that flags periods up to the last data date.
The quickest way to diagnose the problem: put the calendar month, the base measure and the time intelligence measure into a table visual. If the base measure shows figures but the time intelligence measure is blank, the problem almost always lies in the relationship or the date table settings.
Classic or calendar-based time intelligence
Power BI now offers two approaches. Microsoft’s overview of time-based calculations compares them as follows:
| Criterion | Classic | Calendar-based |
|---|---|---|
| Set-up | Moderate | Greater |
| Flexibility | Low | High |
| Calendars | Gregorian or shifted Gregorian | Any: fiscal, retail (445, 454, 544), 13-month, lunar |
| Week-based calculations | Limited | Supported |
| Performance | Slower in some cases | Best |
Microsoft introduced calendar-based time intelligence as a preview feature and recommends it for its performance and flexibility. Here, columns in the date table are mapped to year, quarter, month or week levels in a calendar definition. This is particularly useful for restaurants and retail, where results are compared week by week, or for companies whose fiscal year does not match the calendar year. As the feature is still being developed, check its status in Microsoft’s documentation before deploying it in production reports.
Our view
Time intelligence is one of the most important Power BI capabilities for a finance team, but its value only shows when there is a well-ordered data model underneath it. Rivilė or Finvalda data must be correctly linked to the calendar, account groups must be classified, and reports must refresh without manual work.
That is why we build ready-made Power BI report packages for Rivilė and Finvalda users. In them, revenue, expenses, profit, the balance sheet, sales, inventory and debts are all visible in a single model, and the data refreshes automatically. A manager or accountant gets answers to questions about periods instead of rebuilding Excel spreadsheets from scratch every month.
Where a company has its own fiscal year structure, additional sources such as SharePoint, CRM or logistics systems, or industry-specific needs, we tailor the solution to its processes and existing IT systems.
Analitika360
Ready-built Power BI packages for Rivilė and Finvalda users
If you want period analysis without building date tables and fine-tuning DAX measures, Analitika360 offers ready-built report packages with automatic data refresh.
- Rivilė Basic and Finvalda Basic: €59 a month.
- Rivilė PRO and Finvalda PRO: €89 a month.
- Custom projects: €70 an hour, when you need tailored calculations or additional data sources.
You will find all plans and terms on the business analytics pricing page, and how the packages work is described on the reports without manual work page. A Power BI data gateway is often used to refresh data from on-premises servers.
FAQ
Does time intelligence work without a date table?
Power BI has an automatic date/time feature, but a separate date table is recommended for financial reports. Only with one can you control fiscal years, quarters and consistent period logic across all measures. For classic time intelligence, it must be marked as a date table.
How does TOTALYTD differ from DATESYTD?
TOTALYTD returns the year-to-date value directly. DATESYTD returns a table of dates that you use inside CALCULATE. The result is usually the same, but DATESYTD is handier when you need to add further filters.
How do you calculate YTD if the fiscal year does not match the calendar year?
In the TOTALYTD function, specify the third argument with the last day of the fiscal year, for example “6-30”. The date table must cover full fiscal years. For more complex calendars, calendar-based time intelligence is a good fit.
Why does SAMEPERIODLASTYEAR return a blank?
Most often there is no relationship between the calendar and the fact table, the table is not marked as a date table, or there is simply no data for the previous year in the model. Check the date column’s data type too: a date stored as text will not support the relationship.
Sources
Time intelligence functions (DAX), Microsoft Learn Use DAX time intelligence functions in Power BI semantic models, Microsoft Learn Design guidance for date tables in Power BI Desktop, Microsoft Learn Use DAX time intelligence functions in Power BI, PL-300 lab Implement time-based calculations in Power BI, Microsoft Learn Calendar-based time intelligence (Preview), Power BI Blog
