Showcasing Workday Number in Power BI Using DAX

We’re going to work through something really unique around Time Intelligence inside Power BI. This technique can help you track and determine the exact workday number in Power BI using a variety of DAX formula and advanced logic. You may watch the full video of this tutorial at the bottom of this blog.

Getting around this analysis can help solve an issue around comparing and counting working days in a month for your organization or company.

In other words, you can compare the first workday number in a particular month to the first workday number in another month. It could extend to last month, last quarter, or last year, and so on.

This was actually difficult with standard time intelligence functions in Power BI. But now there are a variety of functions to determine workday number of any year and month in Power BI.

Table of Contents

Sample Insights Showcasing Workday Number in Power BI

This tutorial comes from something I worked on for the Enterprise DNA Support Forum. It talked about the need to compare “like-for-like” within months from a workday perspective.

In this particular case, you’ll see this sample insight that brings about workday and weekend numbers in Power BI.

Here, there are several things that I can show in this table. First, I have placed a Working Days slicer in the upper part of the table.

This is where you can select the data that you want to access, either Weekday or Weekend data. You can also select any time frame – any month and any year!

Why is it important to use this workday number analysis in Power BI? Well, this is most useful when you’re trying to determine if you have more sales on particular workdays or more on weekends.

In this example, we’re looking at the month of May at the beginning of North American summer.

Through this data that I presented, you might be able to evaluate your sales trend and know in advance what will be your sales on the first working day of May next year.

Now, time intelligence calculations won’t directly work in this case. Essentially, you can just evaluate one year versus the prior year regardless of whether it’s a weekday or a weekend.

The challenge here is to determine first which work day is a weekday and which is a weekend. Then, we can branch out into more relevant time intelligence calculations with a bit of advanced logic.

My goal in this tutorial is to help you understand how to break out the workday numbers. I will show you in the next part of this tutorial.

Working Out The Formulas For Workday Numbers

So generally, we’re going to have between 20 to 22 weekday work days in a month. And then, the numbers would probably be around 8-10 weekend days.

In our example below, the month of May has a total of 17 workday numbers and 7 weekend numbers.

To came up with the mentioned insight, I used the formula below.

Integrating RANKX Function

I love using variables and I highly recommend doing so. The most important thing here is that I’ve integrated the RANKX function into our formula.

Inside of the RANKX function, I’m going to find a table called MONTHTABLE.

To find out the MONTHTABLE, I’ll just have to use a filter and evaluate the dates for every single day.

But during this time, we’re going to filter it for either weekdays or weekends. This is made possible because, in my table, I have this particular column where I specified whether a date is a working weekday or a weekend.

So by using this formula, I can filter the weekdays in any particular month.

Next, RANKX will go over every single one of these days, and then evaluate the rank for every single day or row in the virtual table.

In this case, we also need to put the day of a month inside this table.

Lastly, I’ve added the ASC function to evaluate the lowest result in an ascending order.

For example, you can see in the Workday Number column that it returns 1 on May 2.

Then, when the formula is going to evaluate for May 6, it’s going to return 6 in the Workday Number since May 7 and 8 are weekends.

Finding Out The Weekend Number

If we were going to jump over to the weekend number, it’s the same as it will be for the weekday logic, but with minor changes.

Most importantly, you just have to change the virtual table for WEEKEND NUMBER.

After applying the new formula for the weekend number, you’ll see new results here in the virtual table.

You’ll also see under the Weekend Number column that May 1 has 1 value. Meaning, May 1 has the lowest result based on this logic.

Next, the second-lowest ranking is for May 7, followed by May 8. This is all because we used Weekends in our filter. Thus, the formula only evaluates all the weekends of the month, and then returns its value.

In addition to that, if we jump to any different month, you’ll see that this calculation is dynamic. You’ll see that it reevaluates based on any month that we focus on.

I think from this tutorial, you can arrive at a specific question about sales comparison. For instance, the sales on the first working day of November this year versus the first working day sales of last year.

Conclusion

This is some really powerful stuff and I’ve dived into all the key concepts in this tutorial. If you’re completing this analysis, then I highly recommend watching the video. Meanwhile, you can look and understand more how to utilize this logic within your own Power BI models.

Finally, I hope I gave you an interesting aspect of time calculation that you can easily apply to your business.

Enjoy!

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