Managers rarely ask “what were sales?” They ask “how are we doing this year compared with last year?” Power BI answers these questions with time intelligence functions in DAX.
First requirement: a proper date table
Time intelligence only works reliably with a date table that has one row for every day, no gaps, and is marked as a date table in Power BI. Connect it to your sales table’s date column with a one-to-many relationship. If you skip this step, the measures below return wrong or blank results.
Year-to-date and month-to-date
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
Sales MTD = TOTALMTD([Total Sales], 'Date'[Date])
Put Sales YTD in a line chart by month and it climbs through the year, resetting each January. For an Indian financial year starting in April, add the year-end date: TOTALYTD([Total Sales], 'Date'[Date], "31/3").
Previous year comparison
Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales YoY % = DIVIDE([Total Sales] - [Sales PY], [Sales PY])
DIVIDE avoids errors when last year’s value is zero.
Rolling periods
For a rolling three months, use DATESINPERIOD inside CALCULATE. Rolling measures smooth out monthly noise and are popular on management dashboards.
Common mistakes
- Using the date column from the sales table instead of the date table.
- Not marking the date table, so functions behave unexpectedly.
- Showing YoY % without the actual values, which can exaggerate small changes.
- Comparing a partial current month with a full previous month.
Background: why every Power BI report needs a date table and DAX CALCULATE explained.
Power BI and DAX are taught with projects in our 5-month Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
