DAX Time Intelligence: The Complete Guide (YTD, MTD, QTD)

"Show me sales year-to-date." "What's this month versus last month?" "How are we tracking against last year?" Every manager asks these questions, and DAX time intelligence is how you answer them. Get the setup right and these measures take one line. Get it wrong and you'll stare at blank cards wondering why.

If you're an aspiring data analyst in India, this is also exactly the kind of thing interviewers probe. They don't just want TOTALYTD([Sales], 'Date'[Date]), they want to know why it returns blank, what a date table is, and how you handle the April-to-March fiscal year that every Indian company runs on. Let's do all of it properly.

Rule zero: you need a proper date table

Time intelligence functions like TOTALYTD, SAMEPERIODLASTYEAR and DATEADD all rely on one assumption: a continuous, complete date dimension. Not the date column buried in your Sales table, a dedicated calendar table with one row per day and no gaps.

Why a separate table? Your fact table only has dates where something happened. If there were no sales on 15 August (a holiday), that date simply doesn't exist in Sales. Time intelligence needs every day present so it can walk forward and backward across periods. A gap breaks the walk.

Create one with CALENDAR (fixed range) or CALENDARAUTO (auto-detects from your model):

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2022, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "MonthNo", MONTH([Date]),
    "Month", FORMAT([Date], "MMM"),
    "MonthYear", FORMAT([Date], "MMM YYYY"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "FY", IF(MONTH([Date]) >= 4, YEAR([Date]) + 1, YEAR([Date]))
)

Then do the three things everyone forgets:

1. Create a relationship from Sales[OrderDate] to Date[Date] (many-to-one, single direction).
2. Mark as Date Table: right-click the table in the Data pane -> Mark as Date Table -> pick the Date column. This tells DAX it can trust the table for time intelligence.
3. Turn off auto date/time (see below).

Without a marked, related, gap-free date table, your YTD measures return blanks or wrong totals. This is the single most common time-intelligence bug, by a wide margin.

Why "Mark as Date Table" matters

Marking the table does two concrete things. It validates that the chosen column is a unique, contiguous list of dates (it will error if you have gaps or duplicates, catching bugs early). And it removes the hidden auto date/time tables Power BI would otherwise attach to every date column, so all your time intelligence flows through one calendar you control.

Why auto date/time is bad

By default Power BI's auto date/time silently builds a hidden date table behind every date column in your model. It sounds helpful but it causes real problems:

• Model bloat. Each hidden table stores every day from your earliest to latest date. Ten date columns means ten hidden calendars, inflating file size and refresh time.
• Inconsistent logic. Those hidden tables always assume a December year-end. You can't make them fiscal, so YTD silently uses the wrong year for Indian reporting.
• Conflicts. Measures may resolve against a hidden table instead of your real Date table, giving results you can't explain.

Turn it off: File -> Options and settings -> Options -> Data Load -> Time intelligence, and uncheck "Auto date/time" (do it under both Global and Current File). Then rely on your own marked date table.

The base measure first

Everything builds on one base measure. Write it once, reference it everywhere:

Total Sales = SUM(Sales[Amount])

Never re-sum the column inside time intelligence; always pass the base measure. This keeps logic in one place and your code readable.

TOTALYTD, TOTALMTD, TOTALQTD

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

Sales MTD = TOTALMTD([Total Sales], 'Date'[Date])

Sales QTD = TOTALQTD([Total Sales], 'Date'[Date])

TOTALYTD sums from 1 January of the current year up to the latest date in context. On a row for March 2025, it gives Jan + Feb + Mar 2025. Sales MTD accumulates from the 1st of the current month; Sales QTD from the start of the current quarter. Put these in card visuals with a date slicer and you have a running dashboard for free.

Previous period and running total

Two more building blocks you'll use constantly. Previous period (here, prior month) shifts the date context back by an interval with DATEADD:

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

DATEADD is flexible: swap MONTH for DAY, QUARTER, or YEAR, and change -1 to any offset. A running total accumulates every day from the beginning of time up to the current date:

Sales Running Total =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

ALL removes the existing date filter so we can re-filter to "every date on or before the current row," and CALCULATE sums over that growing set. This is the classic cumulative line on a trend chart.

Rolling 3-month average

A rolling (trailing) average smooths noisy monthly data. This one averages the current month and the two before it:

Sales Rolling 3M Avg =
AVERAGEX(
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -3,
        MONTH
    ),
    [Total Sales]
)

DATESINPERIOD returns the set of dates in the 3-month window ending at the current date, and AVERAGEX averages the monthly sales across it. For a true 3-month moving average per month, place it on a monthly axis.

Year-over-year and month-over-month growth

Once the windows above work, comparisons fall into place. For YoY, pair YTD with SAMEPERIODLASTYEAR:

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

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

For MoM, reuse the previous-month measure:

MoM Growth % =
DIVIDE(
    [Total Sales] - [Sales Prev Month],
    [Sales Prev Month]
)

Always use DIVIDE rather than the / operator. DIVIDE returns blank (not an error) when last year or last month is zero, which keeps your visuals clean. Format both measures as percentages in the modelling pane.

The Indian fiscal year (April start)

Here's where most tutorials stop and Indian analysts get stuck. Indian companies report April to March. TOTALYTD assumes a December year-end unless you tell it otherwise. The function takes an optional year-end date argument as the last parameter:

Sales FYTD =
TOTALYTD(
    [Total Sales],
    'Date'[Date],
    "31-03"            -- fiscal year ends 31 March
)

Now "year-to-date" runs from 1 April. On a row for May 2025, Sales FYTD gives April + May 2025, which is what Indian finance teams actually want. The same trailing "31-03" argument works for TOTALQTD when your quarters are fiscal (Q1 = Apr-Jun, and so on).

For fiscal YoY, the comparison just nests:

Sales FYTD LY =
CALCULATE(
    [Sales FYTD],
    SAMEPERIODLASTYEAR('Date'[Date])
)

For grouping and slicers, use the FY column from the calendar above (it bumps the year for April-December). A common label pattern:

FY Label =
"FY" & ('Date'[FY] - 1) & "-" & FORMAT('Date'[FY], "00")
-- e.g. FY2024-25

DATESYTD: the building block when you need filters

TOTALYTD is convenient, but it's really CALCULATE plus DATESYTD. Knowing the long form helps when you need to add extra filters:

Sales FYTD =
CALCULATE(
    [Total Sales],
    DATESYTD('Date'[Date], "31-03")
)

DATESYTD returns the set of dates from fiscal-year start to the current date; CALCULATE evaluates the measure over them. Use this form when you also need, say, a payment-mode filter in the same measure:

UPI Sales FYTD =
CALCULATE(
    [Total Sales],
    DATESYTD('Date'[Date], "31-03"),
    Sales[PaymentMode] = "UPI"
)

For a Flipkart-style monthly trend, the fiscal YTD plus a payment-mode slicer gives a clean "UPI vs Card vs COD, cumulative this FY" view.

Why my time intelligence returns blank or wrong

When a measure misbehaves, walk this checklist top to bottom. The fix is almost always in the first three.

1. No date table, or not marked. This is the number-one cause. If you used Sales[OrderDate] as your only date source, time intelligence quietly returns blank. Build a calendar, relate it, and Mark as Date Table.

2. Gaps in the calendar. If your date table only contains dates that had sales, the period walk breaks. CALENDAR(start, end) guarantees one row per day. The "Mark as Date Table" step will actually error on gaps, so it doubles as a check.

3. You referenced the fact date inside the function. Always pass the date dimension's column: 'Date'[Date], never Sales[OrderDate]. The fact column isn't contiguous, so the function can't navigate it.

4. The relationship is missing or inactive. With no active relationship between Sales and Date, the date filter never reaches the fact table and totals don't change across periods. Check Model view; the line should be solid, not dotted.

5. Wrong fiscal year-end. If Indian YTD looks like it resets in January instead of April, you forgot "31-03". Default December year-end gives the wrong cumulative.

6. Your date range doesn't cover the data. If CALENDAR ends 2024-12-31 but sales run into 2025, the 2025 rows have no matching calendar date and drop out. Extend the range, or generate it with CALENDARAUTO.

7. Auto date/time still on. Hidden tables can hijack the measure. Turn auto date/time off and reference your real calendar everywhere.

8. Filtering on both fact and dimension dates. Slice and relate through the date dimension only. Two competing date filters fight each other and produce surprising blanks.

Best practices

• One marked date table per model, reused by every fact table.
• Build base measures first (Total Sales), then layer YTD/MTD/QTD on top.
• Always pass "31-03" for Indian fiscal reporting and document it in the measure name (Sales FYTD, not a bare Sales YTD).
• Store FY and FY Label columns so users slice by financial year, not calendar year.
• Use DIVIDE for every growth ratio to avoid divide-by-zero errors.
• Verify on a known month by hand before trusting the dashboard, the fastest way to catch a fiscal-year mistake.

FAQ

Why is my YTD blank?
Almost always because you don't have a proper date table, or it isn't marked as one, or you passed the fact table's date column (Sales[OrderDate]) instead of the date dimension's column ('Date'[Date]). Build a contiguous calendar, relate it to your fact table, Mark as Date Table, and reference 'Date'[Date] inside the function. If it's still blank, check that an active relationship exists and that your calendar's date range actually covers your data.

Do I need a date table for time intelligence?
Yes. Functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD require a continuous, gap-free date dimension with one row per day. The date column inside your fact table won't work because it only contains dates where transactions occurred. A dedicated, marked date table is non-negotiable.

SAMEPERIODLASTYEAR vs DATEADD, which should I use?
SAMEPERIODLASTYEAR('Date'[Date]) is a readable shortcut for shifting back exactly one year, ideal for clean YoY comparisons. DATEADD('Date'[Date], -1, YEAR) does the same thing but is more flexible: you can shift by any interval (DAY, MONTH, QUARTER, YEAR) and any number of steps. Use SAMEPERIODLASTYEAR for standard year-over-year; reach for DATEADD when you need prior month, prior quarter, or an arbitrary offset.

How do I handle the Indian financial year (April to March)?
Pass the year-end date as the last argument: TOTALYTD([Total Sales], 'Date'[Date], "31-03"). This makes the cumulative start on 1 April instead of 1 January. Add an FY column to your calendar (IF(MONTH([Date]) >= 4, YEAR([Date]) + 1, YEAR([Date]))) so slicers and groupings respect the fiscal year too.

Why doesn't my YoY change month to month?
Usually a missing or inactive relationship between the fact table and the date table, so the date context never propagates. Check Model view for a solid relationship line. It can also happen if auto date/time is on and the measure is resolving against a hidden calendar rather than your real one.

Should I turn off auto date/time?
Yes, once you have your own date table. Auto date/time creates a hidden calendar for every date column, bloats the model, can't be made fiscal, and sometimes hijacks your measures. Disable it under Options -> Data Load -> Time intelligence, then drive everything through your marked date table.

Time intelligence feels like magic, but it's really just a good date table plus the right one-liner. Set the foundation once and YTD, MTD, QTD, YoY, and MoM all fall into place.

Related: What is DAX and Why It Matters · Practice Power BI

Don't just read. Prove your skill on DevWithData.