How to Exclude Weekends in Power BI

Analyzing your business performance based on a 7-day week can skew your data and hide important trends. If your business primarily operates Monday to Friday, including weekend data can drag down your daily averages and make it harder to see what’s really happening during business hours. This guide will walk you through several straightforward methods in Power BI to exclude Saturdays and Sundays from your calculations, giving you a much clearer view of your business metrics.

Why Exclude Weekends in Your Reports?

Before diving into the “how,” let’s quickly cover the “why.” When you calculate metrics like average daily sales, you want an accurate number that reflects your typical operating day. If you make $5,000 Monday to Friday but only $500 on Saturday and Sunday combined, your 7-day average will look much lower than what you actually bring in during the work week. This can impact decision-making around staffing, ad spend, and inventory.

Removing weekends helps you answer critical business questions more accurately:

• What is our true average daily revenue on the days we are fully operational?
• How long does it take to complete projects when measured in actual workdays?
• Are our weekday marketing campaigns performing as expected, without weekend lulls affecting the average?
• What is the average lead response time in business hours?

By filtering out the noise of non-operational days, you get insights that are directly tied to your core business activities.

The Foundation: A Proper Date Table

Before you can perform any meaningful time intelligence calculations in Power BI, you need a dedicated date table. While you might have date columns in your sales or marketing data, a separate, comprehensive date table is the bedrock of reliable analysis. It acts as a central calendar for your entire data model.

A good date table contains an unbroken sequence of dates spanning your data's entire range. Most importantly for this task, it's where we'll add columns to identify which days are weekdays and which are weekends.

Creating a Simple Date Table with DAX

If you don’t have a date table yet, you can create one easily using DAX. Go to the Data view in Power BI, click "New Table" from the ribbon, and paste in the following formula. You’ll just need to replace 'YourDataTable'[DateColumn] with the actual name of your main data table and its date column.

Date = 
CALENDAR (
    MIN('YourDataTable'[DateColumn]),
    MAX('YourDataTable'[DateColumn])
)

This creates a new table named “Date” with a single column of dates that automatically covers the range of your data. Once you have this table, it's crucial to connect it to your other data tables in the Model view. You do this by dragging the 'Date'[Date] column onto the relevant date column in your other table (e.g., 'Sales'[OrderDate]).

Adding Columns to Identify Weekends

With your date table created, the next step is to add helper columns that will let us flag weekends. We'll add two calculated columns here: one for the day-of-week number and another that works as a simple weekday/weekend flag.

In the Data view, with your "Date" table selected, click "New Column" from the ribbon and use the following formulas for each new column.

1. Day of Week Number

This column will assign a number from 1 (Monday) to 7 (Sunday) to each date. This numeric value is easy to use in formulas.

DayOfWeekNumber = WEEKDAY([Date], 2)

The WEEKDAY function returns the day of the week as a number. The second argument, 2, tells Power BI to start the week on Monday. This means days 1 through 5 are weekdays, and 6 and 7 are weekends (Saturday and Sunday).

2. Is Weekday Flag

This column will simply return TRUE for a weekday and FALSE for a weekend. This makes filtering in your DAX measures incredibly simple down the line.

IsWeekday = IF([DayOfWeekNumber] < 6, TRUE(), FALSE())

Now your date table is ready. It not only has a list of dates but also knows which ones are weekends, setting you up for the next step.

Method 1: Create a "Weekday Only" Measure with CALCULATE

The most common and flexible way to exclude weekends is by creating a DAX measure. Measures are great because they perform calculations on the fly based on the context of your report (e.g., filters from slicers or visuals).

Let's say you have a basic measure for total sales:

Total Sales = SUM(Sales[Revenue])

To create a version of this measure that only includes sales from weekdays, you can use the CALCULATE function. CALCULATE modifies the context of a calculation. In our case, we want to modify the context to only include weekdays.

From the Report view, click "New Measure" and enter this formula:

Weekday Sales = 
CALCULATE(
    [Total Sales],
    'Date'[IsWeekday] = TRUE()
)

Let’s break it down:

• [Total Sales] is the base calculation we want to perform.
• 'Date'[IsWeekday] = TRUE() is the filter we are applying. It tells Power BI to only consider rows where our "IsWeekday" flag is TRUE before it sums up the sales.

You can now use this [Weekday Sales] measure in any visual, and it will automatically show the correct weekday-only total. To see the impact, try creating a card visual for [Total Sales] and another one for [Weekday Sales].

Method 2: Use an Expression in FILTER for More Complex Logic

For some calculations, you might need more control than a simple filter. This is where using filtering functions like SUMX and FILTER comes in handy. This approach is slightly more advanced but gives you a lot of power.

Imagine you want to find the average daily sales, but only for weekdays. If you calculate an average over a filtered table, you need to ensure you're dividing by the count of weekdays, not total days. Here's how you could build a precise average weekday sales measure:

Avg Weekday Sales = 
AVERAGEX(
    FILTER(
        'Date',
        'Date'[IsWeekday] = TRUE()
    ),
    [Total Sales]
)

How this works:

• FILTER('Date', 'Date'[IsWeekday] = TRUE()): This part of the code first creates a new, temporary table containing only the dates that are weekdays.
• AVERAGEX(...): This is an "iterator" function. It goes through the temporary table of weekdays row by row, calculates the [Total Sales] for each day, and then finds the average of those values.

This method ensures your average is genuinely based on business days, providing a much more accurate KPI for your operational performance.

Method 3: Calculating Net Working Days Between Two Dates

Another very common business need is to calculate the number of working days between two dates, like a project start and end date or an order date and shipping date. Power BI doesn't have a built-in NETWORKDAYS function like Excel, but you can easily build your own with the date table we created.

Let's say you have start and end dates in a project table. You would first create a measure that calculates the number of working days for each project.

Working Days =
VAR ProjectStartDate = SELECTEDVALUE(Projects[StartDate])
VAR ProjectEndDate = SELECTEDVALUE(Projects[EndDate])
RETURN
    COUNTROWS(
        FILTER(
            'Date',
            'Date'[Date] >= ProjectStartDate &&
            'Date'[Date] <= ProjectEndDate &&
            'Date'[IsWeekday] = TRUE()
        )
    )

Let's dissect this DAX formula:

• VAR ...: We capture the start and end dates for the currently selected project into variables to make the formula cleaner.
• FILTER(...): This is the core of the logic. It scans our main "Date" table and builds a temporary table that includes only the dates that meet three conditions:
• COUNTROWS(...): Finally, this function simply counts the number of rows in that filtered temporary table, giving us the exact number of working days.

Bonus Tip: How to Handle Public Holidays

Excluding weekends is a great start, but for true business day analysis, you also need to account for public holidays. The best way to do this is by extending your date table.

1. Get a List of Holidays: Find a list of public holidays relevant to your business. You can often find these online and save them as an Excel or CSV file. This file should have at least a [Date] column and a [HolidayName] column.
2. Import the List into Power BI: Import this holiday list as a new table in Power BI (e.g., named "Holidays").
3. Create a Holiday Flag in Your Date Table: Now, you can add a new calculated column to your "Date" table that checks if a date exists in your new "Holidays" table.

IsHoliday = NOT(ISBLANK(RELATED(Holidays[HolidayName])))

(Note: For this RELATED function to work, you must create a one-to-one relationship between your 'Date'[Date] column and 'Holidays'[Date] column in the Model view.)

Finally, you can create a final "IsWorkingDay" column that accounts for both weekends and holidays:

IsWorkingDay = IF([IsWeekday] = TRUE() && [IsHoliday] = FALSE(), TRUE(), FALSE())

Now, just use this IsWorkingDay column in your CALCULATE measures to get the most precise business metrics possible.

Final Thoughts

Removing weekends and holidays from your Power BI reports transforms your data from a blunt instrument into a precision tool. By building a robust date table and using DAX functions like CALCULATE and FILTER, you can accurately measure performance based on your actual operating schedule, leading to smarter, more confident business decisions.

While mastering DAX is a powerful skill, sometimes you just need an answer without writing formulas. This is where we built Graphed to help. Instead of creating calculated columns and measures, you can simply connect your data and ask in plain English, "Show me my average daily sales for weekdays in Q3" or "Create a dashboard comparing my ad spend vs. weekday revenue this month." We handle all the complex data cleanup and calculations in the background, giving you a live, interactive dashboard in seconds.