How to Calculate Last Week Sales in Power BI

One of the most valuable things you can do in Power BI is compare performance over time. While metrics like month-over-month are great for seeing trends, a week-over-week view gives you a pulse on your business right now. This article will show you exactly how to calculate sales from the previous week in Power BI using DAX expressions so you can track performance effectively.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First Things First: You Need a Date Table

Before writing a single time-based DAX formula, you need a proper date table. This is non-negotiable for any serious Power BI reporting. Relying on Power BI's automatic date hierarchies might seem easier, but it will cause inaccuracies and limitations down the road. A dedicated date table gives you complete control over your time intelligence calculations.

A date table is just a table with a continuous sequence of dates - no gaps allowed. It also contains useful columns like year, quarter, month, and week number, which you'll use to slice and dice your data.

Creating a Basic Date Table with DAX

You can create a new date table directly within Power BI. Go to the Data view, select "New Table" from the ribbon, and enter the following DAX formula:

Dates = CALENDARAUTO()

This simple function scans all the date columns in your model and automatically generates a complete date table that spans the earliest and latest dates it finds. It's the quickest way to get started.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Adding Helper Columns

Once your table is created, you'll need a few extra columns for our week-over-week calculation. While still in the Data view with your 'Dates' table selected, add these new columns:

• YEAR: Year = YEAR('Dates'[Date])
• MONTH NUMBER: Month Number = MONTH('Dates'[Date])
• WEEK NUMBER: Week Number = WEEKNUM('Dates'[Date], 2)

Heads up: The WEEKNUM function has an optional second argument that defines the start of the week. Using 1 (or leaving it blank) means sales weeks start on Sunday. Using 2 means you’re working with weeks that start on Monday. Make sure this matches how your business operates!

Set It and Connect It

Creating the table isn't enough. You have two final, crucial steps:

1. Mark as Date Table: From the ribbon, select "Mark as date table" and choose the [Date] column. This tells Power BI that this is your master date calendar for all time intelligence functions.
2. Create a Relationship: Go to the Model view and drag the [Date] column from your new 'Dates' table onto the date column in your primary sales data table (e.g., 'OrderDate' in your 'Sales' table). A one-to-many relationship should form.

With a proper date table in place, you're ready to start building measures.

Step 2: Create a Basic "Total Sales" Measure

Before you can calculate last week's sales, you need a measure for all sales. While you can often drag a numeric field directly into a visual, creating an explicit measure is a DAX best practice. It’s more reusable, easier to manage, and forms the building block for all other complex calculations.

In the Report view, right-click on your sales table in the Fields pane and select "New measure." In the formula bar, enter this simple formula:

Total Sales = SUM(Sales[SalesAmount])

Just replace Sales[SalesAmount] with the name of your sales table and the column containing sales values. This measure now calculates the sum of all sales in whatever context it's placed - whether that's a whole year, a single day, or a specific product.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Calculating Last Week's Sales with DAX

Now for the main event. Our goal is to create a dynamic measure that returns the Total Sales for the previous calendar week. This sounds complex, but by using variables in DAX, we can make the logic clear and easy to follow.

Right-click your sales table again and create a new measure with the following formula. We'll break it down piece by piece below.

Previous Week Sales = VAR _LatestDateInContext = MAX('Dates'[Date]) VAR _CurrentWeek = WEEKNUM(_LatestDateInContext, 2) VAR _CurrentYear = YEAR(_LatestDateInContext) VAR _PrevWeek = IF( _CurrentWeek = 1, 52, _CurrentWeek - 1 ) VAR _PrevWeekYear = IF( _CurrentWeek = 1, _CurrentYear - 1, _CurrentYear ) RETURN CALCULATE( [Total Sales], FILTER( ALL('Dates'), 'Dates'[Week Number] = _PrevWeek && 'Dates'[Year] = _PrevWeekYear ) )

Understanding the Formula, Line by Line

DAX can look a little intimidating, but this logic is relatively straightforward when you dissect it using our variables.

• **VAR _LatestDateInContext = MAX('Dates'[Date])**: This is the secret to making the measure dynamic. Instead of using a fixed date like TODAY(), it finds the latest date in the current filter context. If you select "Week 34" in a slicer, this variable will hold the last day of Week 34.
• **VAR _CurrentWeek = WEEKNUM(...)** and **_CurrentYear = YEAR(...)**: These simply take the date from the first variable and store its corresponding week number and year.
• **VAR _PrevWeek = IF(...)**: This handles the calculation for the previous week's number. In most cases, it’s just the current week number minus one. However, it smartly handles the "January edge case." If the current week is Week 1, the previous calendar week was Week 52 of the prior year.
• **VAR _PrevWeekYear = IF(...)**: Similarly, this variable determines the year of the previous week. It's usually the current year, but if it was Week 1, it accurately switches to the previous year.
• **RETURN CALCULATE([Total Sales], ...)**: This is where everything comes together. You're telling DAX, "Take the Total Sales measure, but change the context."
• **..., FILTER(ALL('Dates'), ...)**: This is the new context you're applying. You start with ALL('Dates') to temporarily remove any existing date filters on the visual. Then, you tell the FILTER function to only return rows from your 'Dates' table where the week number and year columns match the _PrevWeek and _PrevWeekYear variables you calculated.

The result is a measure that will always return the total sales for the complete calendar week that came before the week selected in your report.

Putting It All Together: Visualizing Current vs. Last Week Sales

Now that you have your measures, [Total Sales] and [Previous Week Sales], you can use them to build comparisons. Here are a couple of practical ideas:

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Weekly Performance Table

Create a simple table or matrix visual.

1. Add Year and Week Number from your 'Dates' table to the rows.
2. Add [Total Sales] and [Previous Week Sales] to the values.
3. Finally, create a third "variance" measure like [Total Sales] - [Previous Week Sales] to quickly see the weekly change.

Now you have a clear, week-by-week breakdown of your performance compared to the prior week.

Daily Sales Line Chart

You can also visualize daily performance for a current week against its preceding week.

1. Add a line chart to your report canvas.
2. Add the full Date from your 'Dates' table to the X-axis.
3. Add [Total Sales] and [Previous Week Sales] to the Y-axis.
4. Add a slicer for 'Week Number' to let users choose which week to analyze.

When you select a week in the slicer, you’ll see the current week’s sales trend line overlaid with the previous week's sales - a powerful way to spot daily trends.

Common Traps and Quick Fixes

If your numbers look strange or you’re getting an error, chances are it's one of these common stumbling blocks:

• Incomplete Date Table: The CALENDARAUTO() function is only as smart as the data it has access to. If your sales table doesn't have any sales for a certain period, CALCULATE might return a blank result if your date table is also missing those dates. Ensure your date table covers all potential timeframes you want to analyze.
• Broken or Incorrect Relationships: Double-check the Model view. You must have a single, active, one-to-many relationship between your date calendar's primary date column and the corresponding date in your facts table.
• Mixing Date Columns: Never use the date column from your factual sales table in your visuals or slicers. Always pull year, month, and week number from your dedicated date table. Mixing and matching will break the filter context and give incorrect results.

Final Thoughts

Building week-over-week calculations is a foundational skill in Power BI that opens up more insightful and real-time analysis. With a structured approach using a dedicated date table and clean DAX measures, you can create robust reports that monitor the pulse of your business activities accurately.

Although mastering DAX is powerful, it can feel like a full-time job of writing, testing, and debugging, especially when deadlines are tight. We built Graphed because we wanted to get fast answers from our data without getting stuck in formula hell. Instead of writing DAX, you can simply connect your data sources and ask questions like, "Show me last week's Shopify sales compared to the week before as a bar chart," and get a live-updating dashboard in seconds. You can go from question to insight without touching a line of code.