Time Intelligence in DAX with a custom date table

Time Intelligence is the set of DAX functions that lets you calculate periods such as Year-to-date (YTD), Month-to-date (MTD), Year-over-Year (YoY) comparisons and parallel periods. To work correctly, they require a continuous, marked date table.

Create the date table in DAX

dDate =
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "Year",      YEAR([Date]),
    "Quarter",   "Q" & QUARTER([Date]),
    "Month",     MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM", "en-US"),
    "MonthYear", FORMAT([Date], "MMM YYYY", "en-US"),
    "Week",      WEEKNUM([Date], 2),
    "Weekday",   WEEKDAY([Date], 2),
    "IsWorkday", IF(WEEKDAY([Date],2) <= 5, TRUE, FALSE)
)

Mark the table as a Date Table in Table Tools → Mark as Date Table using the Date column.

Time Intelligence measures

Sales YTD = TOTALYTD(SUM(fSales[Revenue]), dDate[Date])

Sales MTD = TOTALMTD(SUM(fSales[Revenue]), dDate[Date])

Sales Prior Year = CALCULATE(
    SUM(fSales[Revenue]),
    SAMEPERIODLASTYEAR(dDate[Date])
)

YoY % = DIVIDE(
    SUM(fSales[Revenue]) - [Sales Prior Year],
    [Sales Prior Year]
)

Fiscal year vs. calendar year

If your fiscal year starts in July, use the year_end_date parameter:

Sales YTD Fiscal = TOTALYTD(
    SUM(fSales[Revenue]),
    dDate[Date],
    "6/30"   -- fiscal year end: June 30
)

Conclusion

A well-built, correctly marked date table unlocks the full potential of Time Intelligence functions. Invest 30 minutes building a reusable date table — you will use it in every Power BI project.