In today’s blog, we’ll discuss Problem of the Week #10. The problem of the Week is an ongoing series where we solve real-world scenarios through DAX and Power Query. This week, we’ll deal with a time period analysis. You may watch the full video of this tutorial at the bottom of this blog.
The problem this week is something I’ve brought in from my own work that can be used in hundreds of cases, regardless of the industry. This is about dates with the element of time. The task is to be able to define a particular date period, but also select a start time and an end time.
This is for business owners who want to know their sales or know the number of their customers, or the number of products they are producing from 12AM on January 23rd to 12PM on January 25th. The second part of the problem will take it one step further by having the same calculation but considering only the weekdays, which is a common scenario for organizations that have employees on a shift schedule.
The Task At Hand: Time Period Analysis
So the first part of the task is actually just between a date period and a time period selected. We have a start time, an end time, and a sum of the total customers.
The second part of the task is to trim that down so that we’re only getting the working days, which for this example is from Monday to Friday.
The model has been built for this problem.
We also have dummy data within the fact table. There are only a couple of days in here, but these should be enough for us to tackle this problem.
The problem will be solved through DAX only, so that means absolutely no Power Query. What’s interesting about this challenge is that it just builds on a few simple DAX techniques that you need to pull together. It’s a matter of breaking this problem down into smaller baby steps.
Some of the questions we get all the time on the forum are calculations on workdays between two dates. These calculations get more complex because of the additional element of time, where the time between dates comes up a lot. I think this is going to be a great problem and a great reference to start.
We’ve had some previous Problem of the Week scenarios that have been difficult. This one is more intermediate than some of the ones we’ve had recently. So if you’ve been sitting on the fence and watching the Problem of the Week but haven’t dived into it, this would be the perfect jumping point. Commitment-wise, this problem is less complex than some of the ones we’ve been doing, but there’s still a lot to learn and a lot to get out of it.
The Flexibility Of Power BI
So, I want to encourage everybody to take a shot at this one. We always find that there are some real innovations in the way people go about attacking these problems post-solution. There’s always something for us to learn from the solutions that come in.
The great thing about Power BI is that nobody looks at the problem the same way.
There’s probably an optimal way of doing it, but I don’t think there’s a right or a wrong way, as long as we’re getting that solution.
How To Tackle This Time Period Analysis
There’s probably a couple of techniques you can use to tackle this problem. You can harvest your slicer selection into your calculation. And then you’ll need to have a start date, a start time, and an end time to put onto a slicer. You’ll also probably use some DAX to generate this as well.
Getting to the end result will require joining three or four techniques into one calculation. This is where the variables come in.
But it will be interesting to see the solutions of those who won’t take the variable approach. You can also do this with measure branching as well.
A Sneak Peek At Power BI Challenge #13
I’d also like to give you a quick preview of what we will be seeing in the coming weeks. For the Power BI Challenge, we’ll have something that can be used for every industry. It’s something that we should all be doing for every single project and development in Power BI.
This challenge will be slightly different from the normal challenges since it’s not really a business problem. Without giving too much away, it will be a very interesting challenge about a task that nobody really wants to spend much time doing. Hopefully, this upcoming challenge will be a great way for us to automate this process.
***** Related Links *****
Adding Workday And Weekend Numbers Into Your Date Table
Showcasing Workday Number in Power BI Using DAX
Working Out Sales Periods Using DAX in Power BI: Weekday vs. Weekend
Conclusion
I encourage you to join the ongoing Problem of the Week so you can improve your DAX and Power Query skills. For more learning resources, check out the rest of our website and subscribe to Enterprise DNA TV.
Haroon
[youtube https://www.youtube.com/watch?v=mVG3J-6kQ5M&t=87s?rel=0&w=784&h=441]
