Introduction

DAX Time Intelligence functions are essential for sales analytics in Power BI. Whether you’re building an executive dashboard or a detailed sales report, comparing performance across time periods helps you uncover trends and make data-driven decisions.

In this guide, you’ll learn three of the most useful DAX functions by using a sample data:

• TOTALYTD — Year-to-date calculations
• SAMEPERIODLASTYEAR — Direct year-over-year comparison
• DATEADD — Flexible period shifting (years, quarters, months)

All examples work in Power BI and SQL Server Analysis Services (SSAS) Tabular. For Excel Power Pivot users, the same formulas apply directly.

---

What You Need Before Starting

Open Power BI Desktop. Have these two files ready:

1. Date_Table.csv — Date dimension with Year, Month, Quarter columns OR create your Date Table using CalendarAuto() and get the associated columns.
2. Sales_Data.csv — Sales data with Date, Product, Revenue columns

---

Part 1: Setting Up Your Data Model

Step 1.1: Import the CSV Files

ACTION: Click Get Data → Text/CSV

1. In Power BI Desktop, click Get Data from the ribbon
2. Select Text/CSV or File → Text/CSV
3. Navigate to the folder you saved the sample datasets and select Date_Table.csv
4. Click Load (or Transform Data if you want to preview first)
5. Repeat the same steps for Sample_Sales_Data.csv

Step 1.2: Verify Your Data Loaded Correctly

ACTION: Check the tables

1. In the left panel, click Data view
2. Select Date_Table — verify it shows 25 rows (Jan 2023 to Dec 2024)
3. Select Sales — verify it shows 48 rows of transactions

Fig 1: Image showing the table view of the semantic models (Sales and Date_Table dataset)

---

Step 1.3: Create the Relationship

ACTION: Open Model view and create relationship

1. Click Model view (icon in left panel)
2. Drag Sales[Date] and drop it onto Date_Table[Date]
3. Verify a one-to-many relationship is created (Date_Table on the one side)
4. Make sure the relationship is active (solid line, not dashed)

Fig 1.1: Image showing proper data modelling of the semantic models (Sales and Date_Table datasets)

---

Step 1.4: Mark the Date Table

ACTION: Mark Date_Table as your date dimension

1. Right-click on Date_Table in the Fields pane
2. Select Mark as date table → Mark as date table
3. Choose Date as the date column from the dropdown
4. Click OK

Fig 1.2: Image showing how to Mark as date table

Important: Time Intelligence functions require a proper date table marked as a date dimension. Without this, the formulas in this guide won’t work correctly.

---

Part 2: TOTALYTD — Year-to-Date Calculations

What is TOTALYTD?

TOTALYTD is a DAX function that calculates a cumulative total from the start of the year to the current date in your filter context.

Syntax:

TOTALYTD(<expression>, <dates>, [<filter>], [<year_end_date>]). Now, let’s do it.

---

Step 2.1: Create the Revenue YTD Measure

ACTION: Create a new measure in the Sales table

1. In the Fields pane, right-click on Sales table
2. Select New measure
3. In the formula bar at the top, type:

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

4. Press Enter to confirm

Step 2.2: Build a Table Visual to Test

ACTION: Create a table visual

1. Click Report view
2. In the Visualizations pane, click Table icon
3. Drag these fields to the table:
   • Date_Table[Month] → Rows
   • Revenue YTD → Values

Fig 2.0 Matrix and Card visuals showing the Revenue YTD measure

Step 2.3: Filter to March 2024

ACTION: Apply a filter to see YTD through March

1. In the Filters pane, expand Filters on this visual
2. Drag Date_Table[Year] to the filter area
3. Filter to 2024 only
4. Drag Date_Table[Month] to the same filter area
5. Filter to 1, 2, 3 (January, February, March)

Fig 2.1: Image showing the filtered Revenue YTD for January to March 2024

---

Step 2.4: Understand the YTD Calculation

ACTION: Verify the math

Look at the breakdown for March 2024:

• Jan 2024: Widget A (8,500) = $20,500
• Feb 2024: Widget A (9,100) = $20,900
• Mar 2024: Widget A (9,800) = $24,800
• Total YTD through March: 20,900+ 66,200

Fig 2.2 Image showing the filtered values between January to March 2024 for validation

---

Part 3: SAMEPERIODLASTYEAR — Year-over-Year Comparison

What is SAMEPERIODLASTYEAR?

SAMEPERIODLASTYEAR is a time intelligence DAX function that shifts your current date context back by exactly one year, making year-over-year comparisons straightforward.

Syntax:

SAMEPERIODLASTYEAR(<dates>)

---

Step 3.1: Create the Revenue LY Measure

ACTION: Create a new measure for Last Year revenue

1. Right-click on Sales table → New measure
2. Enter this formula:

Revenue LY = CALCULATE( SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Date_Table[Date]))

3. Press Enter

Step 3.2: Add Revenue LY to Your Table

ACTION: Add the new measure to the visual

1. In your table visual, drag Revenue LY to the Values section (next to Revenue YTD)
2. Keep the filter on Year = 2024, Months 1-3

Fig 3.0: Image showing the calculation of the Revenue Last Year 

---

Step 3.3: Compare March 2024 vs March 2023

ACTION: Filter to March only to see the direct comparison

1. Change the filter to show only Month = 3 (March)
2. Observe that Revenue LY shows March 2023 values

Fig 3.1: Image showing how Revenue Last Year filtered the month of March the previous year (2023)

Calculation for March 2024:

• March 2024: Widget A (15,000)  Widget B (9,800) = $24,800
• March 2023: Widget A (8,900)  = $26,300

---

Step 3.4: Add Year-over-Year Growth Percentage

ACTION: Create a YoY Growth % measure

YoY% answers one critical business question: Are we doing better or worse than we were at this exact time last year, and by how much?

1. New measure in Sales table:

YoY Growth % = DIVIDE([Revenue YTD] – [Revenue LY], [Revenue LY])

2. Add this measure to your table visual

Fig 3.2: Image showing the calculation of YoY% growth with a focus on March 2024

Expected Results for March 2024:

• YoY Growth % = (26,300) / 26,300 = 1.52%

---

Part 4: DATEADD — Flexible Period Shifting

What is DATEADD?

DATEADD shifts dates forward or backward by any interval – years, quarters, months, or days. It’s more versatile than SAMEPERIODLASTYEAR though both mean same and can replace one another.

Syntax:

DATEADD(<dates>, <number_of_intervals>, <interval>)

---

Step 4.1: Create Previous Year Measure with DATEADD

ACTION: Create Revenue PY measure

1. New measure in Sales table:

Revenue PY = CALCULATE(SUM(Sales[Revenue]), DATEADD(Date_Table[Date], -1, YEAR))

2. Press Enter

Fig 4.0: Image showing the similarity between Revenue PY (DATEADD function) and Revenue LY (SAMEPERIODLASTYEAR function) calculations

---

Build a Summary Card Visual

ACTION: Create KPI cards for your dashboard

1. Click Card visual in Visualizations pane
2. Drag Revenue YTD to the card
3. Create additional cards for:
   • Revenue LY
   • YoY Growth %
   • Revenue PQ

Part 6: Common Pitfalls and How to Avoid Them

Pitfall 1: Missing Date Table

Problem: Time Intelligence functions require a proper date table.

Solution: If you haven’t marked your date table:

1. Right-click Date_Table → Mark as date table → Choose Date column

Pitfall 2: Filter Context Matters

Problem: YTD gives unexpected results when filtered to a single product.

Solution: Understand that filters cascade. If you filter by “Widget A only”, YTD respects that filter.

Pitfall 3: SAMEPERIODLASTYEAR vs DATEADD

Problem: Not sure which to use.

Solution:

• Use SAMEPERIODLASTYEAR for exact 1-year offset
• Use DATEADD when you need flexibility (multiple years, quarters, months, days)

Summary

Function Quick Reference

Function	Best For	Example
TOTALYTD	Cumulative yearly totals	Show YTD revenue through current month
SAMEPERIODLASTYEAR	Simple YoY comparison	Compare March 2024 vs March 2023
DATEADD	Flexible period shifts	Compare Q1 2024 vs Q4 2023

Questions about other DAX topics? Let me know in the comment box!