Time intelligence with a proper date table

Time intelligence in DAX is easy once the date table is right — and impossible when it is not. Nine out of ten "SAMEPERIODLASTYEAR returns blank" problems are date-table problems.

Why the auto date/time is not enough

Power BI can create a hidden date hierarchy per date column. It bloats the model, cannot be shared between tables and does not know your April–March fiscal year. In File → Options → Data Load, switch Auto date/time off and build one real Date table.

Build the date table

Date =
VAR MinYear = YEAR ( MIN ( Sales[InvoiceDate] ) )
VAR MaxYear = YEAR ( MAX ( Sales[InvoiceDate] ) )
RETURN
ADDCOLUMNS (
    CALENDAR ( DATE ( MinYear, 4, 1 ), DATE ( MaxYear + 1, 3, 31 ) ),
    "Year",        YEAR ( [Date] ),
    "Month No",    MONTH ( [Date] ),
    "Month",       FORMAT ( [Date], "MMM" ),
    "Quarter",     "Q" & QUARTER ( [Date] ),
    "Fiscal Year", "FY" & IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ) + 1, YEAR ( [Date] ) ),
    "FY Month No", IF ( MONTH ( [Date] ) >= 4, MONTH ( [Date] ) - 3, MONTH ( [Date] ) + 9 )
)

Three rules make it a valid date table:

1. It contains every day, with no gaps, from 1 April of the first year to 31 March of the last.
2. The date column is a true date type with no duplicates and no time part.
3. You right-click the table → Mark as date table and choose Date.

Then sort Month by Month No (Column tools → Sort by column), or your matrix shows Apr, Aug, Dec.

The core measures

Total Sales = SUM ( Sales[NetAmount] )

Sales MTD = CALCULATE ( [Total Sales], DATESMTD ( 'Date'[Date] ) )

Sales FYTD = CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date], "31/3" ) )

Sales SPLY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

YoY % = DIVIDE ( [Total Sales] - [Sales SPLY], [Sales SPLY] )

The "31/3" argument tells DATESYTD the fiscal year ends on 31 March, so the running total restarts on 1 April instead of 1 January — exactly what Indian finance teams expect.

DATEADD is the flexible cousin when you need an arbitrary shift:

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

The partial-period trap

On 12 September, Total Sales covers 12 days while Sales SPLY covers all 30 days of last September. Every YoY number looks terrible. Fix it by comparing like with like:

Sales SPLY LFL =
VAR LastSale = MAX ( Sales[InvoiceDate] )
RETURN
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] ),
    'Date'[Date] <= EDATE ( LastSale, -12 )
)

Measure	1–12 Sep 2025	Reads as
Total Sales	18.4 L	Actual to date
Sales SPLY	41.9 L	Full Sep 2024 — misleading
Sales SPLY LFL	17.1 L	1–12 Sep 2024 — fair

Common mistakes

• Filtering by Sales[InvoiceDate] in visuals instead of 'Date'[Date]; time intelligence then ignores your slicer.
• A date table that stops at today, so future-dated targets disappear.
• Leaving a time component on the date column, which silently breaks the relationship match.
• Using TOTALYTD and passing the fiscal year end as the third argument — that slot is a filter; the year-end string is the last argument.