How Essential are The Time Formulas of PowerBI

Since we just built our Master Calendar, today we move to the most famous “Basic” skill in Power BI: Time Intelligence (DAX).

This is the ability to compare different time periods. In Excel, comparing “This Month vs. Last Month” requires a lot of manual copying and pasting. In Power BI, we use two simple formulas to do it automatically.

Today’s topic is The “Big Two” Time Formulas: TOTALYTD and SAMEPERIODLASTYEAR.

Why These Formulas are Essential

Business owners rarely ask “How much did we make today?” They usually ask:

1. “How are we doing so far this year?” (Year-to-Date)
2. “Are we doing better than we were this time last year?” (Year-over-Year)

If you don’t have a Calendar Table (which we built yesterday), these formulas will return errors. If you do have one, they are magic.

The Lab Data: Your Sales History

To see this work, you need data that spans at least two years. Create this in Excel and import it:

Table: SalesData

| Date | Revenue |

| :— | :— |

| 01/15/2025 | 1000 |

| 06/10/2025 | 2000 |

| 01/15/2026 | 1500 |

| 02/01/2026 | 500 |

Action Step 1: Calculating Year-to-Date (TOTALYTD)

This formula “adds up” everything from January 1st to the current date.

1. The Prep: First, create a basic measure for Total Sales:Total Sales = SUM(SalesData[Revenue])
2. The Action: Right-click your table and select New Measure.
3. The Step: Type this formula:$$Sales YTD = TOTALYTD([Total Sales], MasterCalendar[Date])$$
4. The Result: Drag this into a Card. It will show $2,000 (The sum of the 2026 sales so far). If you filter for 2025, it will show $3,000.

Action Step 2: Comparing to Last Year (SAMEPERIODLASTYEAR)

This is the “Time Machine” formula. It tells Power BI to look at the current date and “jump back” exactly one year.

1. The Action: Right-click your table and select New Measure.
2. The Step: Type this formula:$$Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(MasterCalendar[Date]))$$
3. The Result: Create a Table Visual.
   • Drag MasterCalendar[Month] and MasterCalendar[Year] into it.
   • Drag [Total Sales] and [Sales Last Year] into it.
4. Observation: Look at the row for January 2026. You will see $1,500 in the Total Sales column, and $1,000 in the Sales Last Year column.

The “Basics” Checklist for Time Intelligence

• Requirement: Your Calendar Table must be connected to your Sales Table.
• The “Measure” Rule: Always use a Measure (like Total Sales) inside these formulas, not a raw column.
• The Date Trigger: Always use the Date column from your MasterCalendar, not the one from your Sales table.

Your Action Summary

1. Create the Total Sales measure.
2. Create the Sales YTD measure to see cumulative growth.
3. Create the Sales Last Year measure to compare performance.
4. Put them all in a Line Chart to see “This Year” vs “Last Year” as two separate lines.