Mastering Power BI Time Intelligence DAX: YTD, QTD, MTD, % of Total, YOY & More

In Power BI, Time Intelligence DAX functions are the secret sauce behind powerful, dynamic reports. Whether you're tracking performance year-over-year or evaluating current trends vs past periods, these functions help you analyze business metrics over time all with elegance and flexibility.

In this article, I’ll break down the most important Time Intelligence DAX calculations, show you the syntax, and explain how each can be used in real-world business scenarios.

Prerequisite

It is import that we have a Calendar table in the Power BI data model and extablish the many-to-one relationship across the unique date column in the date dimension table and the date column in the fact table that contains many date of transaction recorded. In the screenshot below, I have already created a dimDate table and established the necessary relationship as seen in the data model.

Calculating TotalSales

First, we want to calculate TotalSales which shows ho much is generated as a result of business activities across all-time period. In this data model, Price column is located in nthe dimProduct table while the Units column is in the fSales table. Hence, we have to use the RELATED DAX function to lookup the price in the dimProduct Table and muitipled by the Units in the fSales table using row-context - evaluating one row at a time. To calculate that, I issued the following DAX formula as seen in the screenshot.

1. Year-To-Date (YTD)

We want to compute the first time-intelligence calculation which tracks cumulative performance from the start of the year to the current date. This is great for annual revenue pacing, sales team performance, or budget tracking. For that, I issued the DAX in the screenshot below and used the Sales YTD measures in Table visual with Year, Month and TotalSales calculated columns and measure. We can see the TotalSales cummulatively.

2. Previous Year (PY)

We want to calculated the Previous Year (PY) which is useful to compare performance with the same period in the previous year. It’s essential for YoY comparisons, seasonality analysis, and growth trends. To calculate, I authored the DAX as seen below and used the PY in the Table visual. From the calculation, we can, for example, see that Sales (12,547,740), in January of 2015 compared to the relatively low 3,675,145 in January of 2015

3. Year-over-Year Growth (%)

The YoY Growth % measures growth or decline over time. This is used by executives to track business expansion, product success, or market shifts year-over-year. To compute, I implemented the DAX formula, as seen in the screenshot below, formatted the measure using percentage formatting, and used it in the table visual. Still on the January of 2014 and January of 2015, the YOY Growth % returned -70.71%

4. Rolling 12 Months (R12M)

The Rolling 12 Months smooths out monthly fluctuations and gives a long-term view of performance. Used in strategic planning, trend detection, and financial reporting. To calculate, I issued the DAX formula below and used it in the table

Time Intelligence DAX functions are essential tools for any Power BI developer or business analyst. They help transform static reports into interactive insights, making it easier for decision-makers to understand what’s really happening in the business.

By mastering these patterns, you're not just building reports — you're delivering valuable, time-based narratives that drive action.