Day 8: DAX — Time Intelligence

A Date table (also called Calendar table) is a table with one row per date covering the full range of your data, with columns for Year, Quarter, Month, Week, Day, and any custom fiscal periods. Power BI's time intelligence DAX functions (SAMEPERIODLASTYEAR, DATESYTD, etc.) require a properly marked Date table to work correctly. Without a Date table: date hierarchies do not work properly, time intelligence functions fail or give wrong results, and sorting months alphabetically instead of chronologically becomes a problem.

Date Table = CALENDAR(DATE(2024,1,1), DATE(2026,12,31)) This creates a single Date column from Jan 1 2024 to Dec 31 2026. Then add calculated columns: Year = YEAR('Date Table'[Date]) Month = MONTH('Date Table'[Date]) Month Name = FORMAT('Date Table'[Date], "MMMM") Quarter = "Q" & QUARTER('Date Table'[Date]) Weekday = FORMAT('Date Table'[Date], "DDD") Alternatively: CALENDARAUTO() automatically detects the min and max dates in your model. After creating, go to Table Tools → Mark as Date Table and set the Date column.

Revenue YTD = CALCULATE(SUM(Sales[Revenue]), DATESYTD('Date Table'[Date])) DATESYTD returns all dates from the beginning of the year to the current date in context. So if a visual shows August 2026, Revenue YTD shows the total from January 1 2026 to the last date in August. For fiscal year YTD (e.g. April to March): DATESYTD('Date Table'[Date], "31-03") uses March 31 as the year-end.

Revenue Last Year = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR('Date Table'[Date])) Revenue YoY Growth % = DIVIDE(SUM(Sales[Revenue]) - [Revenue Last Year], [Revenue Last Year]) SAMEPERIODLASTYEAR shifts the date filter back by exactly one year. If the current context is August 2026, it evaluates for August 2025. The YoY Growth % measure returns the percentage change. Format as percentage. Add conditional formatting in the visual to colour positive growth green and negative red.

DATESYTD is a date function that returns a table of dates from the start of the year to the current date — it modifies the filter context. TOTALYTD is a shortcut that combines CALCULATE and DATESYTD: TOTALYTD(SUM(Sales[Revenue]), 'Date Table'[Date]) is equivalent to CALCULATE(SUM(Sales[Revenue]), DATESYTD('Date Table'[Date])). TOTALYTD is simpler to write but less flexible — you cannot use it inside another CALCULATE. Use DATESYTD with CALCULATE for complex scenarios.