Trending March 2024 # Power Bi Between Dates Working Hours Solution # Suggested April 2024 # Top 8 Popular

You are reading the article Power Bi Between Dates Working Hours Solution updated in March 2024 on the website We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 Power Bi Between Dates Working Hours Solution

In today’s blog, I will demonstrate an approach to Access Analytic’s Start Stop Challenge where, using Power BI, between-dates total working hours will be calculated. You can watch the full video of this tutorial at the bottom of this blog.

The task is to calculate the total number of hours for a staff member on each month and date. I presented start and end dates that can cross over months and take the status into account dealing with possible typos. 

Let’s go to the Power Query editor. 

Here’s our data for this challenge. We have a table with columns for the Staff Name, Start Date, Start Time, Stop Date, Stop Time, and Status.

Nothing will happen yet and that’s fine because we write a lowercase “e” and the “Exclude” in our data that starts with an uppercase “E”.

Of course, we could have someone who have erroneously typed “Exclude” without an initial “E.” In that case, we can create some additional logic to go along with the situation. 

We can test if the word does not include an “X” by extending our function and using Text.Contains. To do that, add or Text.Contains ([Status], “x”, Comparer.OrdinalIgnoreCase.

Let’s now head to the main part of this challenge. We’ll add a custom column using the UI and then switch to the editor. 

At this point, we’re only interested in bringing the data that we’ll need later on. And because we’ll work with multiple fields, let’s create a record using the record initializers, represented with square brackets. 

We can then format our variables through the Advanced Editor window. This is how the Advanced Editor window should look like. 

Then, let’s put our variables in separate new lines.

Now, we can expand the logic that we need. So, instead of a record, we could also use a nested let expression here. Note that either of those will work because we have multiple variables or field names with values assigned to them.

Our Start Date is currently written as a text value and we need to transform that into a proper date format. To do that, wrap the function Date.FromText around it. 

For the Start Time which is also formatted as a string, we need to convert it to a number. Then, we can divide it by a hundred and round it to remove any decimal places. 

Using the Number.From function, divide our Start Date value by 100. Then, wrap them inside the next function, Number.Round, and add , 0 at the end to round off without decimal places. 

For End Date and End Time, we’ll just copy the same functions we had in Start Date and Start Time respectively, and replace them with the appropriate variables.

Once the variables are properly formatted, create another field in our record for some additional logic. Create a new line by pressing Enter. 

We need to construct a list of dates from the first date all the way up to the end date. We’ll call those dates LD and we’ll use the function List.Dates. 

The first parameter of this function is the start as date which would be our SD. Then, it wants a count as number or the difference between the start date and the end date. We can get that using the Number.From function followed by (ED – SD) + 1. Note that the “+1” is not shown in the next sets of screenshots, but there should be a +1.

The third parameter is called the step as duration and we want an increment of one day. We can get that using Duration.From (1). 

We’ve created a list with dates from the very first start date up to the stop date. What we want next is to create a list of times that goes along with that. 

Let’s call this LT for list times. LT can have a day period of a single day which requires a slightly different logic than if it spans across multiple days. So that’s something that we have to create a condition for. 

We want the condition to say “if our start date is the same as our end date, then end time minus the start time.” The results should be in a list of format so we use the list initializer represented by { }. 

Now, if we have a date range that spans multiple days, then the first date will be 24 minus the start time. We will add this to our condition as the else statement. Again, format that as a list using the curly brackets.

At this point, we haven’t accounted for the other whole days so we’ll use the ampersand symbol (&) to append multiple lists. For each whole day, create a list with the value 24 representing the 24 hours we have in a day. We’ll use List.Repeat for that. 

With the List.Repeat function, create a list that contains 24 and repeat that a number of times by counting the number of days in LD. To achieve that, use List.Count (LD)  then subtract 2 because we have a separate list for our start date and we’ll create another list for the end time. 

In essence, this will create a list with only 24 hours for each whole day. 

For the end time, we can append it again as a list using ampersand, Then, using the list initializers, let’s call the ET. 

So far, we have two big lists—a list of dates and a list of times—with their lengths being equal to one another. From those two lists, we can construct a single table. 

In a new line, create another variable for the table which we’ll call t and use the function Table.FromColumns. This function requires lists as list and we’ll use our LD. 

Our LD contains individual dates from the start date up to the stop date. We can transform that list of dates to the end of month date value by passing LD and calling the Date.EndOfMonth function. Then in the next line, let’s get our LT as well. 

Press Enter to create another line and set our table as type table. Our table will have two columns, the first column being a date column. So, create a Date column and call the Date.Type function.

The second column will be our Hours column and it will be an integer. Thus, we’ll create an Hrs column and call Int8.Type.

In the Custom column, we can see that each Record is changed to Table. 

From the table that we’re looking at, there are only two columns of interest—the staff name and our custom column. We can use projection to retain those two columns alone from this table. 

Go to the formula bar, and beside our closing parenthesis, select the fields that we want to keep by placing them inside square brackets. Then press the check mark.

We should get this result.

Our table should appear like this.

All that’s left to do now is to aggregate these values. To do that, select the Staff Name column and go to Transform. 

Our final output will look like this. 

In this blog, you’ve seen one approach to Access Analytic’s Start Stop Challenge. You learned how to calculate the total number of hours worked by each staff member in a month, and how to deal with possible typos in your data. 

With this technique and Power BI, between-dates working hours can easily be calculated in your own organization, or as a practice to deepen your Power BI knowledge and skills. 

You're reading Power Bi Between Dates Working Hours Solution

Power Bi Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit more complex subject in comparison to the concept which we discussed in my previous tutorial. You can watch the full video of this tutorial at the bottom of this blog.

In the previous tutorial, we saw how to calculate the percentages in a hierarchical form. We showcased how to calculate ranking over a specific set of data of a particular set of products, either in the form of a product category or subcategory. Also, in the case of the region, it is either in the form of cities, territories, or countries.

This time around, we will go one step further in the calculation of hierarchies. We’ll learn how to calculate ranking when all the different sets of fields are combined in a single set of hierarchies.

Let’s head over to Power BI and see how it is being done. In this scenario, we have created a set of hierarchies where the first level of the hierarchy is in the form of territories.

The second level is in the form of customers.

And the third level is in the form of product names.

So, what’s the logic behind creating this type of hierarchy? We need to find out how we are performing at each and every territorial level. Then, inside each territory, we need to determine who is our best-performing customers.

Additionally, we want to figure out what are the best products our customers like to purchase in their respective territories over a period of time.

To begin with, we will use the wrong approach how to calculate Power BI ranking by using the RANKX function. By doing this, we will understand why the conventional way of doing this will not yield the correct results.

It’s also worth looking at the errors so we can rank the correct measures to evaluate the results for each and every level of the hierarchy in the right manner.

To save time, I copied and pasted the measure based on a simple RANKX function. With this, we created three variables for each of the hierarchies using the simple RANKX function. Additionally, we used the ISINSCOPE function since we are dealing with hierarchies.

Let’s now load this measure and check out the results. As we can see, the territory with the highest total sales is ranked first and the territory with the lowest total sales is ranked last. So far, we are getting the correct results.

Next, check the results for the products to see whether they are also correctly arranged or not.

However, we observed that the customers and products which don’t contain any sales value are still getting ranked. We can stop evaluating these by using the ISBLANK function.

As shown below, we can still see that the first territory and customers for that particular territory are still evaluated correctly.

Scroll down to see how our next territory is ranked.

At this stage, we can now see that there is a problem because the Queensland territory was ranked as second earlier but now, it is evaluated as third.

To be sure, let’s also check how our products are evaluated.

Similarly, the New South Wales territory and the State Ltd customer were both ranked first in the earlier scenarios, but are now second.

Therefore, we cannot use the conventional method of calculating ranking in the case of hierarchies because drill down actually means that we move down from the first level to the next level.

Let’s name our measure as Ranking – Right Measure and create three variables for calculating the ranking for the respective hierarchies. 

Type in VAR_Territory_Ranking for our first variable. Inside this variable, the first process is to create a virtual table using SUMMARIZE (Regions, Regions[Territory]). We referenced this to the regions table since territories are coming from that table.

Once we have created our virtual table, inside this, we need to create a virtual column using the ADDCOLUMNS function. This will help us to evaluate the ranking for that particular territory hierarchy.  

Then, let’s name our virtual column Territory Ranking and use the RANKX function. We want to rank our highest sales first and the lowest sales last. So, we’ll convert this virtual table into a scalar value by wrapping it up inside the SUMX function.

We implemented the exact same technique for the other two variables such as the VAR_Customers_Ranking and the VAR_Products_Ranking.

Of course, we used the ISINSCOPE function and called out each of our variables against that particular level of hierarchy.

So far, what we’ve done is to create variables for each levels of the hierarchy, and placed each variable against that particular field using the ISINSCOPE function.

It’s time to test our formula and check the results by bringing the measure into our matrix visual and expanding it.

As shown below, the ranking at a territory level is getting evaluated correctly.

Let’s drill down further and check our customer ranking. The New South Wales territory and State LTD customers are ranked correctly as well.

This time around, let’s see how our Queensland territory is evaluated. Finally, it is now ranked accordingly.

For our product levels, it is nice to see that they are also ranked perfectly.

If we’ll also take a look at our second and third customers, we also got the correct results.

Lastly, let’s get rid of the ranking against the blanks by replacing the reference of the respective dimension table names with the Sales table name. 

We do this because the Sales table has a lineage with each of these dimension tables. For example, if sales haven’t taken place for a specific product against a particular customer in that territory, then by default, ranking will not be evaluated against that particular data point. 

So, let’s go ahead and replace our reference of dimension tables with the Sales Table.

Finally, let’s check the results one last time. 

It worked! We can now see that our ranking is only limited to a data point where we have a sales value. 

Calculating ranking in a hierarchical form involves complexity especially if you’re dealing with different sets of fields combined in a single set of hierarchies, but it’s worth it because it can really drill down to the next level.

Another important thing to note is that if you would like to see calculate Power BI ranking change based on the selection made into the slicer, then instead of using the ALL function, you can make use of the ALLSELECTED function while evaluating the rankings.

So that’s it for me, I hope you enjoyed this tutorial. There are plenty of resources and further learnings that you can access easily on the Enterprise DNA website, so please check it out.

All the best,


Ranking Visualization In Power Bi – Dynamic Visual

In this blog post, I want to teach you how to show your customers moving through groups over time in a dynamic way. You can do this ranking visualization in Power BI using a unique slicer. You may watch the full video of this tutorial at the bottom of this blog.

The first thing we need to do is to create the underlying calculations. Since we already have the calculation for our revenue from This Quarter, we need to create the revenue calculations for the Previous Quarter and Two Quarters Ago.

I’ll create a new measure and name it as Revenue LQ. We’ll use the CALCULATE function, and reference the Total Revenue. Then use the DATEADD function, reference the Date table column, and then write minus 1 quarter.

The DATEADD function is such a good formula to use because of its flexibility to quickly change to any timeframe.

I’m going to copy this measure and paste it in the formula bar to create a new measure. Let’s call this one Revenue 2 Qs Ago, and then change the quarter from -1 to -2.

So now we’ve got all the revenues from the previous quarters. Let me just show you the revenue from last quarter.

The next step is to create a ranking formula that indicates how a client is ranked. We’ll create a new measure and call it Revenue Period Ranking.

To start things off, I’m going to use the RANKX function and reference ALL of my customers. Then I’ll write an expression, which is Total Revenue, and put it in a descending order.

Now we can see our clients’ ranking.

I also want to embed the ranking measure we’ve made into the Total Revenue by Customer Names visualization in the left so I’m going to use some variables.

I’m going with VAR then CurrentPeriodRank. The great thing about variables is that you can name them whatever you like.

Then I’ll copy this down twice in the formula bar and change their names to LastPeriodRank and TwoAgoPeriodRank.

Then I’m going to input the Revenue LQ and Revenue 2 Qs Ago calculations that we just did.

The next step is to match these up to whatever is selected in the slicer. We’ll use RETURN and then make a SWITCH TRUE statement.

We’ll use the SELECTEDVALUE function and reference Time Frame.

If the time frame is equal to this period, we want that to equal to CurrentPeriodRank.

At this point, I’m just bringing a lot of elements together here. I’m going to copy this portion of the formula twice.

I’m going to change This Period to This Quarter, Previous Quarter, and 2 Quarters Ago.

The key here is that no matter what selection we make in the Time Frame slicer, the numbers under the Total Revenue column shouldn’t change.

With Q2 2023 as the selected filter for Quarter & Year, this is the Total Revenue value for our customers and their ranking for This Quarter.

But we also want to see how these people ranked in the previous quarters. To do this, we just need to select the Previous Quarter and Two Quarters Ago from the slicer. Then the ranking number is going to change for each corresponding customer.

The last thing we need to do is go into the Data Color section, and then go to the Conditional Formatting area.

We’ll to the Rules section and then find the measure Revenue Period Ranking.

If the value of the number is greater than or equal to 1 and is less than or equal to 20, we want it equal to the darker blue color.

Let’s create a new rule. If it is greater than 20 and less than 20,000, then we want this to be equal to a much lighter blue color.

This is going to highlight the Total Revenue by Customer Names visual.

To come up with this group ranking visualization in Power BI report, we used a couple of different techniques. We have a supporting table which determined the metric we’re looking at. Then within the measure itself, we used logic derived from a variable. And then to finish it off, we used conditional formatting within the visual itself to showcase some interesting insight.

I hope that with this tutorial, you’ll be inspired on how you can carry out these techniques in your own reports. There are so many different ways that you can use logic like this. That’s definitely the key takeaway that I want you to learn from this tutorial.

If you liked this content, then watch the accompanying video and don’t forget to subscribe to Enterprise DNA TV.



Power Bi Challenge 14 – Emergency Services Analytics

We’ve been very busy here at Enterprise DNA as we continue to level up our content so that all of you can maximize more learning opportunities as far as Power BI is concerned. Of course, one of the best ways for you to put all of this knowledge to practice is by joining our Power BI Challenge.

We just concluded the 13th round, which was all about acing report documentation. This time, we’re moving on to emergency services analytics.

Power BI has seen quite a number of updates in the past few months, and we’re excited to see how these improvements can impact the tool’s real-world application. That’s why for this challenge, we looked into one of the most dynamic and in-demand industries, which is emergency services.

The data is anonymized and random, but that shouldn’t stop anyone from creating a compelling report that tells a clear story of what emergency services are doing well and what areas they can further improve.

To help you come up with deeper analytics, we’ve also put together a few things that you should consider in your report.

Deadline for submission is on July 11.

The Power BI Challenge is an initiative that we started last year not just to add some fun, friendly competition around the Enterprise DNA Forum, but also to explore just how endless the possibilities are when it comes to using Power BI as a tool. Seeing that this is the 14th round and that we’ve managed to present unique real-life scenarios that cover a different industry each time, this certainly proves how dynamic Power BI truly is.

The challenges have fostered a culture of collaboration and sharing throughout the community. We started out with active members of our Enterprise DNA Forum participating, but we have since been seeing non-members jumping in as well.

It has truly been inspiring seeing a lot of beginners plucking up the courage to join their first challenge and then seeing their submissions improving until they submit reports that rival that of our experts by their 3rd or 4th round. This is why we continue to encourage those who have been thinking twice about joining just because they feel they lack the experience to do so. There’s no better learning experience than hands-on application.

Winners get a range of different prizes, especially for first-time participants.

Winning Enterprise DNA Member – free membership that can be passed on to a non-member who will benefit from the member-exclusive resources

Winning Non-Enterprise DNA Member – free membership

Most Innovative Entry – open to both members and non-members; overall winner can also claim this title, with a free membership as the prize

First Time Participant Winner – must be a member to qualify; winners get to choose their prize from this list of tools and resources that can help them further improve their reports and dashboards

All submissions also become part of our Power BI Showcase, where Enterprise DNA members can download and use them for some hands-on practice.

How do we judge who wins? We just stick to the 4 pillars that make a great Power BI report:

Data loading and transformation

Data modeling

DAX calculations

Reports and visualizations

So keep these in mind when you work on your submission. Staying true to these four elements can help you snag those prizes.

Here’s how you can join the challenge.

Download the data set from the forum.

Post the screenshot of your report and explanation in LinkedIn along with the name of the challenge (e.g. Power BI Challenge 14 – Emergency Services Analytics)

Include this in your post: I accepted the #EnterpriseDNAPowerBIChallenge and hyperlink the challenge post from the Forum.

Submit your PBIX files to 

[email protected]

We’re looking forward to seeing the amazing reports you come up with!

All the best,

Enterprise DNA Team

Creating A Visual Tooltip In Power Bi

I’m going to show you my most favorite visualization idea around tooltip in Power BI. I saw it demonstrated really well in a Power BI challenge submission and I just want to show you how far you can take it. It’s not that difficult at all and it can add so much value to your report. You may watch the full video of this tutorial at the bottom of this blog.

This visualization technique using the Power BI tooltip will keep your consumers engaged and it enables you to compact more information into a singular report page. You don’t need to build comprehensive reports with ten pages or more. I’m going to show you how you can do that with this great tooltip idea.

Historically, this is what a tooltip has looked like, right? But what if you could customize this tooltip and create a visual tooltip? Wouldn’t that be awesome?

To do this, I first go to the paint roller, to the page size, and then I select tooltip.

This gives me this unique page that is optimized for tooltips. I’m going to grab a table visualization and put some information in it. I want to be able to hover over a customer on my report page, which will then show me some information about the customer.

In my table visual, I put my Date table as well as my Revenue and Orders. I then adjust the table accordingly to fit the page.

Next, I put a simple pie chart below the table. And then I’m going to grab the Channel from my Sales table because I want to see the channel in which a lot of these sales are happening. And I’ll also bring in my Revenue. Then, I just simplify this table and change the data labels into Category, percentage total.

I’m just going to rename this page as Details. You can rename it whatever you’d like. And, because this is a tooltip, I’m going to just hide it because we won’t need it in the application itself.

Lastly, I turn on the Tooltip in the Page information to make this work in the report page.

And now, when I hover over the visualization, I have this dynamic visual tooltip. I just love this idea. It’s so cool!

You can do a lot of other interesting things in the tooltip page that you’ve created. You can play around with the colors of the background, text or fonts, etc. It doesn’t need to be white. Just be creative and think about what could make the visualization pop.

This is just another amazing idea about how you can maximize the Power BI tooltip. I don’t think there’s any other analytical tool that would enable us to do this. It’s pretty epic how a consumer is able to see all this information so quickly and easily.

There’s so much variability in report development now inside Power BI compared to what it was even just a few years ago. Microsoft has done some amazing work!

This is a great visualization feature in Power BI. I recommend that you implement this technique in your own reports. You just need to think strategically about how your model is set up for this to work.



Using Color Schemes For Power Bi Data Visualization

Creating a high-quality color palette for your Power BI data visualization is essential to make your reports look compelling and professional. You may watch the full video of this tutorial at the bottom of this blog.

I’m constantly seeing some reports that use a generic color palette from Power BI, which affects the visualization and representation of their insights.

I personally can’t stand these colors as they just don’t do your work any favor when engaging your consumers into your insights.

So, if you want to create great reports, you need to spend a bit more time finding a good and coherent theme that you can implement in your models.

Let’s try to use this report as an example. I’ll be showing you some of the colors that I am using in a particular report, and how I use them in combination with one another.

I used the same theme throughout my entire report. I tried to match good colors that will work together.

This report was utilized during the Enterprise DNA learning summit last May, 2023. We went through six different workshop sessions over three days, and a lot of analytical insights were discussed around how you can create really compelling Power BI reports. A big part of that was related to visualization.

Now, some of you might think that it’s quite hard to find good color combinations. During this tutorial, I’ll discuss how you can create really great and compelling color themes in your reports. I’ll run through my process and show how I create color palettes from scratch.

First of all, I’ll be showing you this Enterprise DNA Power BI Showcase page. This is where you can get some inspiration for colors.

You can also use the live demos for each report and dashboard. If you want to download all of these, you just have to upgrade to a Enterprise DNA membership. This is certainly something to consider if you really want to master Power BI visualizations and analytical techniques.

In creating color palettes, I mostly use two key websites. The first one is called Color Palette FX.

This website automatically generates palettes of color based on an image that you already have. For instance, if you have a company website, logo, or any other company-related image, we can just place it into this image box.

Then, it would automatically come up with a palette of colors based exactly on the image that you have uploaded. 

Let’s check out this image here.

The image above is the one that I used for the sample report that I previously showed. It doesn’t look exactly the same because I only chose the particular colors I wanted to use. But you can still see that there were some colors from the image which I utilized in this report.

If you want to download this resource, all you have to do is to register for the learning summit. During the summit, you will not just learn about creating compelling good reports and models in Power BI, but you’ll also be able to download this resource, and see how I’ve developed the visualization. 

Then find the file that we want to upload.

Another thing that you’ll get if you come to the learning summit is a text file. This file contains settings on how your themes need to be set up. This format is what you’ll be using so you can completely implement the color palette in your Power BI reports.

Then, you should paste it into this format from the text file.

After that, you need to turn it into a json file. You can do this by saving it with a .json file extension. Then, you may import the file after saving it. And that’s the core of what I do for color themes. I always like to start with a random image of my choice, and then use a website to generate colors from the image.

If I’m not yet satisfied with the generated palette from the previous website, I also go to this really great website called Coolors. This is also free to use. It can generate a palette of similar colors that work together.

For example, if we want to use this particular color, we just need to copy the Hex color code value.

Then, let’s paste it into this part of the Coolors website.

 And then let’s try to use this light greenish color.

Paste it again here, and make sure to lock both colors.

Finally, just press the space bar. As you’ve probably noticed, these three colors are now creating the other parts of our palette and generating different colors that we can utilize in our report.

You can now get it into your model to have this coherent color scheme that we can utilize and implement in our reports.

That’s how you implement great colors within Power BI. Primarily, I gain inspiration from an image. Then I upload that image in a web-based tool to generate the color palette for me. From generating all of these unique codes for my color palette, I then create my palette template and integrate this into Power BI.

Another important design tip: utilize good color combinations and do not overdo it.

I highly recommend working through the video to understand both my work and thought processes on how to generate great color palettes in Power BI for your own work.

For many more design and report creation tips, check out the Dashboarding & Data Visualization Intensive course module at Enterprise DNA Online.

Also, don’t forget to check out the Enterprise DNA Showcase page for more inspiration on how to set up reports in Power BI.

Enjoy working through this detailed video.


Update the detailed information about Power Bi Between Dates Working Hours Solution on the website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!