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.
***** 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
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
[youtube https://www.youtube.com/watch?v=Pu1_jzQvkmU&w=784&h=441]
