How to Exclude Weekends From Calculations

In Power BI, you can always exclude weekends from calculations in some types of analysis, such as financial modeling or resource planning, and there are different ways to do that depending on the context and specific needs of your analysis.

In this article, I will explain different ways you can exclude weekends from your calculations in Power BI.

Let’s get started.

Table of Contents show

Introduction: How to Exclude Weekends From Calculations

The following are different ways through which you can exclude weekends from calculations in Power BI:

Use a DAX formula

In Power BI, one common way to exclude weekends from calculations is to use a DAX formula to filter out weekend dates from a date column.

For example, imagine you have a column in your dataset called Sales_Date that contains dates including weekends.

You can create a new calculated column that only includes dates from weekdays by using the WEEKDAY function in DAX.

To filter out weekend dates from the Sales_Date column, you can create a new calculated column using the DAX formula below:

Weekday_Date = FILTER('Table_Name',[Sales_Date] <= TODAY() && WEEKDAY([Sales_Date],1) < 6)

In this formula, Table_Name represents the name of the table in which the Sales_Date column is located.

This formula will create a new column called Weekday_Date that only includes dates from the Table_Name that are less than or equal to today’s date and have a weekday number less than 6 (which corresponds to Monday to Friday).

So, you can then use this new column in your calculations instead of the actual Sales_Date column.

Create a custom date table

Another way to exclude weekends from calculations in Power BI is to create a custom date table that only includes weekdays.

To create a custom date table, you can use the New Table option in the Modelling tab to create a table with a DAX formula.

You can enter the below DAX formula in the formula bar:

Weekday Dates = FILTER(Calendar, WEEKDAY(Calendar[Date],1) < 6)

In this formula, Calendar represents the name of the original date table. The formula creates a new table called Weekday_Dates that only includes dates from the Calendar table that have a weekday number of less than 6 (which corresponds to Monday to Friday).

So, you can then use this new table in your calculations instead of the original date table.

FAQs

Can you also exclude holidays from calculations?

Yes, you can exclude holidays from calculations in Power BI by adding the holidays to the filter conditions in your DAX formula.

To do this, you can create a table that lists all the holidays and use the FILTER function to remove them from your data.

Can you include half-days as working days?

Yes, you can. By modifying the filter conditions in your DAX formula. Also, you can specify the start and end times for each working day and filter out the non-working hours.

Conclusion: How to Exclude Weekends From Calculations

These are just a few examples of how you can exclude weekends from calculations in Power BI.

However, the method to use will depend on the specific needs of your analysis and the structure of your data.

I hope you enjoyed reading this article.

Thanks for reading.