# Time Period Analysis – Intro To Problem Of The Week #10

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.

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.

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

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...

## Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

## Getting Started with DAX in Power BI: A Beginnerâ€™s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...