🚀 Data Analyst Roadmap — Part 27

POWER BI LEVEL 6 — DAX TIME INTELLIGENCE

Time-based analysis is one of the most important things you will do in Power BI.

Businesses commonly ask:
👉 How much did sales grow this month?
👉 How does this year compare with last year?
👉 What was the sales total year-to-date?
👉 Which month had the highest sales?
👉 Are we growing or declining over time?

DAX Time Intelligence helps answer these questions.

🔹 1. You need a proper Date Table

Before using time-intelligence functions, create a dedicated Date table.

Example:
Date =
CALENDAR(
DATE(2024,1,1),
DATE(2026,12,31)
)

Then create useful columns:
• Year
• Month
• Month Number
• Quarter
• Year-Month

Sort Month by Month Number so that January → February → March →... → December instead of alphabetical ordering.

Mark the table as a Date table in Power BI.

🔹 2. Total Sales

Start with a basic measure:
Total Sales =
SUM(Sales[SalesAmount])

This becomes the foundation for most time-based calculations.

🔹 3. Year-to-Date — TOTALYTD()

YTD means Year To Date. It calculates the cumulative value from the beginning of the year up to the current date.

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

If the current month is June, the measure calculates: January + February + March + April + May + June

🔹 4. Previous Year Sales

To compare the current period with the same period last year:
Sales LY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('Date'[Date])
)

If the current visual shows March 2026, this measure returns March 2025 sales.

🔹 5. Year-over-Year Growth

Now compare current sales with last year:
YoY Growth =
[Total Sales] - [Sales LY]

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

Example:
• Current Year Sales = ₹120 lakh
• Previous Year Sales = ₹100 lakh
• Growth = ₹20 lakh
• Growth % = 20%

🔹 6. DATEADD()

DATEADD() shifts the current date context.

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

Previous Year:
Sales Previous Year =
CALCULATE(
[Total Sales],
DATEADD(
'Date'[Date],
-1,
YEAR
)
)

You can shift by:
• DAY
• MONTH
• QUARTER
• YEAR

🔹 7. Month-over-Month Growth

First calculate previous month sales:
Sales PM =
CALCULATE(
[Total Sales],
DATEADD(
'Date'[Date],
-1,
MONTH
)
)

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

Example:
• January = ₹10 lakh
• February = ₹12 lakh
• MoM Growth = 20%

🔹 8. TOTALMTD() and TOTALQTD()

Similar to TOTALYTD():

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

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

So you can analyze:
• MTD → current month progress
• QTD → current quarter progress
• YTD → current year progress

🔹 9. Why Date Tables Matter

Suppose your sales table contains Order Date, Customer, Product, Sales. You could try to perform time calculations directly on Order Date, but a dedicated Date table gives you a consistent calendar for:
• ✔ Year
• ✔ Quarter
• ✔ Month
• ✔ Week
• ✔ YTD
• ✔ MTD
• ✔ QTD
• ✔ Previous period
• ✔ YoY
• ✔ MoM

This becomes especially important when working with multiple fact tables.

🔹 10. A Common Mistake

Don't create every time calculation as a calculated column.
Avoid creating separate columns for:
• Previous Year Sales
• YTD Sales
• MoM Growth
• YoY Growth

These are generally better as measures because they need to respond dynamically to filters and report context.

🎯 Interview Questions

1️⃣ What is Time Intelligence in Power BI?
It is the use of DAX functions to perform calculations across dates and periods.

2️⃣ Why do we need a Date table?
It provides a consistent calendar structure for reliable time-based analysis.

3️⃣ What does SAMEPERIODLASTYEAR() do?
It returns the corresponding period from the previous year.

4️⃣ What is the difference between MTD, QTD and YTD?
• MTD = Month To Date
• QTD = Quarter To Date
• YTD = Year To Date

5️⃣ What does DATEADD() do?
It shifts the current date context by a specified number of days, months, quarters, or years.

Double Tap ❤️ For More