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

6 comments

This is a very unique example of Time Intelligence in Power BI, especially when working with workdays and weekends.

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.

request from an Enterprise DNA member regarding weekday and weekend

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.

inside the Date table

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

time intelligence power bi

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.

time intelligence power bi

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

time intelligence power bi

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.

time intelligence power bi

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

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

***** Related Links*****
Time Comparison For Non Standard Date Tables In Power BI
Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars
Working Out Sales Periods Using DAX In Power BI: Weekday vs. Weekend

***** Related Course Modules*****
Time Intelligence Calculations
Mastering DAX Calculations
DAX Formula Deep Dives

***** Related Support Forum Posts*****
Total Sales Sum To Next Business Day
Number Of Working Days
DateDiff To Only Calculate Number Of Work Days
For more workday weekend queries to review see here…..

Enterprise DNA Events

6 comments on “Adding Workday And Weekend Numbers Into Your Date Table: Time Intelligence Technique In Power BI”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.