How to Calculate Last Year YTD in Power BI

Comparing your performance this year to the same period last year provides the context you need to understand true growth. Calculating Last Year's Year-to-Date (LY YTD) is a fundamental part of tracking this, but getting it right in a tool like Power BI can feel intimidating. This tutorial breaks down the process into simple, manageable steps, showing you exactly how to build the DAX measures you need for accurate, insightful, year-over-year reporting.

Why You Need LY YTD Anyway?

Tracking your basic Year-to-Date (YTD) total is fine for knowing where you stand right now, but it's a number without context. Are your YTD sales of $500,000 good? Without knowing last year's figures, it's impossible to say. Were you at $400,000 or $600,000 at this same point last year?

That’s precisely the question LY YTD answers. It provides an "apples-to-apples" comparison. For example, grabbing performance data up to June 20th of this year and comparing it to the full-year results from last year is misleading. The last half of the year hasn't happened yet! Comparing your YTD numbers with the same YTD period from the previous year removes seasonality and gives you a much clearer signal about your current trajectory.

This is critical for questions like:

• Are our Q2 sales growing faster or slower than last year's Q2 momentum?
• Is this month's dip in website traffic part of a larger trend or just a blip?
• How is our new marketing campaign’s performance tracking against last year’s benchmark at this exact point in the quarter?

LY YTD turns raw data into a directional compass for your business.

The Essential First Step: Your Date Table

Before writing a single time-based DAX calculation, you need a proper Date Table (sometimes called a Calendar Table). This is non-negotiable in Power BI. Time intelligence functions rely on a complete, unbroken sequence of dates to work correctly.

Why can't you just use the date column from your sales data? Because transactional tables often have gaps - days where no sales occurred, weekends, or holidays. DAX functions need every single day in the period (e.g., Jan 1, Jan 2, Jan 3...) to properly shift and compare timeframes.

Creating a Simple Date Table with DAX

If you don't have a corporate date dimension, you can easily create one inside Power BI. Go to the "Data" view, click "New Table" from the Home or Table tools ribbon, and enter a DAX formula like this:

DateTable = CALENDARAUTO()

The CALENDARAUTO() function scans all date columns in your model and automatically creates a table with one column, "Date," that contains every single day from the earliest date in your data to the latest. It's the quickest way to get started.

Once created, you can add more useful columns like Year, Month Name, or Day of Week. For example, to add a Year column:

Year = YEAR('DateTable'[Date])

To add a Month Name column:

Month Name = FORMAT('DateTable'[Date], "mmmm")

Mark as Date Table & Create a Relationship

After creating your table, you need to tell Power BI what it is:

1. Select your 'DateTable'.
2. Go to the "Table tools" tab in the ribbon.
3. Click "Mark as date table" and select the primary date column (e.g., 'Date').

Finally, go to the "Model" view and create a one-to-many relationship by dragging your 'DateTable'[Date] column onto the date column of your main fact table (like 'Sales'[OrderDate]). The "one" side should be your Date Table, and the "many" side your sales table.

With this foundation in place, you’re ready to write your measures.

Step 1: Calculating Your Current Year-to-Date (YTD) Measure

Before jumping into LY YTD, we first need the two building blocks: a base measure (like Total Sales) and a measure for the current year's YTD.

The Base Measure

First, let’s create a simple measure to sum up our sales revenue. In the "Report" view, right-click any of your tables and select "New measure."

Total Sales = SUM('Sales'[Revenue])

Putting this logic into a measure (instead of just dragging the 'Revenue' column into a visual) is a best practice. It’s reusable and forms the foundation for all our more complex calculations.

The Current YTD Measure

Now, let’s build on that. We can use the TOTALYTD function to calculate our year-to-date sales. It’s quick, efficient, and very readable.

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

Let’s break down what this does:

• TOTALYTD(...): This is the YTD function.
• [Total Sales]: The first argument is the measure we want to evaluate - our base sales calculation.
• 'DateTable'[Date]: The second argument is our date column from our dedicated Date Table. The function uses this to figure out where the year starts and stops.

Now, if you put this measure in a card visual or a table alongside months, you'll see a running total that resets every January.

Step 2: Calculating Last Year's Year-to-Date (LY YTD)

With our [Sales YTD] measure ready, calculating its equivalent for the prior year is surprisingly straightforward. The primary method involves combining CALCULATE and SAMEPERIODLASTYEAR.

The Classic Approach: CALCULATE + SAMEPERIODLASTYEAR

Create another new measure with the following DAX formula. This is the most common and intuitive pattern you'll find.

Sales LY YTD = 
CALCULATE(
    [Sales YTD],
    SAMEPERIODLASTYEAR('DateTable'[Date])
)

This elegant little formula is doing some heavy lifting, so let's walk through it:

• CALCULATE(...): This is the most powerful function in DAX. Its job is to modify the context of a calculation. Think of it as saying, "Calculate this expression, but with this filter applied."
• [Sales YTD]: This is our expression. We're telling CALCULATE that the number we want is our existing YTD sales total.
• SAMEPERIODLASTYEAR('DateTable'[Date]): This is our new filter. This function takes the set of dates currently being evaluated (for example, January 1st to June 20th, 2024), and shifts that entire period back by one year (so it returns January 1st to June 20th, 2023).

So, the formula reads almost like plain English: "Calculate the Sales YTD, but do it for the same period last year."

An Alternative for Power Users: Using DATEADD

Another way to accomplish the same goal is with the DATEADD function. This can be more flexible if you later want to calculate things like "Last Month YTD" or go back two years.

Sales LY YTD (DATEADD) =
CALCULATE (
    [Sales YTD],
    DATEADD('DateTable'[Date], -1, YEAR)
)

Here, DATEADD('DateTable'[Date], -1, YEAR) simply tells DAX to shift the current date context back by an interval of -1 YEAR. For standard LY YTD, it has the same result as SAMEPERIODLASTYEAR, but it gives you another powerful tool for your DAX toolbox.

Step 3: Visualizing Your YTD vs. LY YTD Performance

Now for the fun part. Drag your measures onto your report canvas to see them in action.

Here are a few effective ways to visualize your new YTD comparison:

• Cards for a High-Level KPI: Place three Card visuals side by side. The first for Total Sales, the second for Sales YTD, and the third for Sales LY YTD. Add a date slicer to your report, and you can see a snapshot of performance for any selected time frame.
• A Matrix for Detailed Comparison: Create a Matrix visual. Put Month Name on the Rows, and then add Sales YTD and Sales LY YTD to the Values. This gives you a clear monthly breakdown of how your cumulative totals are stacking up year-over-year.
• A Line Chart for Trend Analysis: A line chart is perfect for visualizing momentum. Place Date or Month on the X-axis and drop both Sales YTD and Sales LY YTD into the Y-axis field. You'll instantly see if your growth this year (the top line) is outpacing last year's performance (the bottom line).

Common Issues (And How to Fix Them)

Getting a blank or incorrect result for LY YTD is a common frustration when you’re starting out. Here are a few things to check.

• My LY YTD is coming up blank! The most common culprit is your Date Table. Does it include dates for last year? The CALENDARAUTO() function should handle this, but if you built it manually, ensure your date range extends back far enough. Also, double-check that you've correctly marked it as a date table and that its relationship to your transaction table is set up properly.
• The numbers don't match my expectations. Confirm your base [Total Sales] measure is correct and Filters are cleared. Make sure you don't have conflicting filters applied at a slicer or page level. If your company uses a fiscal calendar (e.g., your year ends in March), you’ll need to specify that in your TOTALYTD function like this: TOTALYTD([Total Sales], 'DateTable'[Date], "3/31").
• It works, but feels slow on a large dataset. DAX is incredibly efficient, but with billions of rows, optimization helps. Keeping your data model clean (removing unused columns) is the best first step. Using variables within your DAX measures is also a great practice for both readability and performance, though for a simple LY YTD measure, it's not strictly necessary.

Final Thoughts

Calculating LY YTD in Power BI comes down to building a few clean DAX measures on top of a solid foundation - your date table. By combining the CALCULATE workhorse with specific time intelligence functions like SAMEPERIODLASTYEAR, you can create the powerful, relevant comparisons needed to understand your business's true performance trajectory.

While mastering DAX is a fantastic skill, we also know that for marketers, founders, and sales leaders, the priority is getting timely answers, not writing custom formulas. We built Graphed to do just that. You can connect sources like Shopify, Google Analytics, and Salesforce, then just ask, "Show me a line chart comparing sales YTD vs last year YTD," and get a live, automated dashboard in seconds. Our goal is to let you focus on what the data means, not on the hours it takes to wrangle it.