# Adding Workday And Weekend Numbers Into Your Date Table: Time Intelligence Technique In Power BI

This is a very unique example of Time Intelligence in Power BI, especially when working with workdays and weekends. You may watch the full video of this tutorial at the bottom of this blog.

This tutorial came ftom a request from the Enterprise DNA Support Forum, where a member needed to get the day for each weekday and the day of each weekend into their Date table.

I’ve seen this quite a lot especially on the Enterprise DNA Support Forum, where users are trying to analyze like for like within months or within years. This is a unique requirement but one that I can see having some broad applications, especially if you need to compare like for like days between different months.

This was all to be placed within a calculated column as well, which meant considering the context of each row inside the Date table.

We dive into the RANKX function and how you can use it to calculate this unique insight.

Once you work through the logic and you see how it’s applied inside the Date table (which is a must-have table inside any data model), you’ll be surprised at how seamlessly you can run this calculation in Power BI compared to completing this in any other tool.

## Working Out The Day Type

Before we go into the formulas, we need to create this column first, Day Type.

Based on the DayInWeek column, we want to have a column that shows what is a workday and what is a weekend.

So if the DayInWeek is not 6 or 0, which is Saturday and Sunday in our Date table, it’ll be equal to a Workday. If it’s those numbers, it’ll equal to a Weekend. We can see the results in our Day Type column.

From here, we can then number these Day Type results using a formula. The logic for workday filters only days in that would be considered a workday. To achieve that, we need to create a table that only evaluates the workdays. From there, we’ll rank those days from 1 to 21 or 22.

So let’s check out the formula.

## Working Out Workday Number

We use variables (VAR), CurrentMonth and MonthTable, in our formula. We first understand what month and year we are in because the month and year is how we’re going to determine the bounds in which this revised virtual table is going to be. Then, within each different row, we remove all context from Dates using the FILTER function.

This formula is looking only at workdays and filtering by the CurrentMonth as well, which is our first variable.

If the Day Type equals to Weekend, it’ll equal BLANK. Then, we want to create a rank of days, by using RANKX, and put the virtual table (MonthTable). Finally, we evaluate or rank the DayOfMonth.

## Working Out Weekend Number

Now let’s just jump over to the Weekend Number formula. It’s quite similar to the Workday Number formula. All we need to change is the virtual table that we’re evaluating, which is Weekends

And then again, we’re just going to use RANKX to rank each of the DayOfMonth that a weekend day might be, and that’s going to give us the results.

## Conclusion

This is a great Time Intelligence technique in Power BI to add workday and weekend numbers to your Date table. From here, you can run some time intelligence based on the work.

This gives you an idea on how to write some logic and formulas that allow you to do time intelligence based on these workdays and weekends. I’ve actually created a lot of videos around how to do time intelligence with non-standard Date tables and 445 calendars. Check out the links below to those tutorials.

Enjoy working through this one. It’s a great idea for new and interesting analyses.

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations â€” the...

## Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...

## Announcing The Enterprise DNA Submit A Showcase Program

Today we are launching a brand-new program here at Enterprise DNA. We are looking to collaborate with...

## Inventory Management Reports To Show Trends In Sales

This Enterprise DNA Power BI Showcase focuses on Inventory Management. You may watch the full video of...

## Huff Gravity Model Analysis in Power BI

In this tutorial, we'll learn how to do a Huff Gravity Model analysis in Power BI. We can use this...

## Excel Hacks Every Business Should Know

No matter what industry you belong to, having a better understanding of Microsoft Excel gives you a...

## Power BI Report Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...