Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

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

by | 7:00 pm EDT | May 12, 2021 | Power BI

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

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...