For this post, I would like to talk about a recent Power BI tip that I came up with for a member of the Enterprise DNA forum. The question dealt with a useful pattern in terms of looking at the first N business days or first N billable days of a given month versus the same period in the previous month. You can watch the full video of this tutorial at the bottom of this blog.
TJ Henneman wanted to compare the first 5 billable days of the current month with the first 5 billable days of the previous month, and then on Day 10, Day 15, and Day 20. Note that he wants to look only at the billable days, so this does not include weekends and holidays. You can check out his post here.
I think that there’s a really interesting power query solution to be had here. I also worked with an interesting DAX solution on this problem. I will go over the latter in another tutorial.
I also want to walk you through a recent reporting technique I used for Power BI Challenge #16. I used a scrolling KPI indicator that will work really well for this particular problem.
But for this Power BI tip, I’ll touch on the power query solution. Let’s jump into Power BI and take a look at our data. For this one, I simulated the data using the practice dataset tool that we put together at Enterprise DNA, which you can use for free. This is great for putting together a basic star schema dataset and for testing out solutions.
Our Data Model
Our data model uses our Extended date table plus a series of dimension tables. We also have a Sales table with three years of partial data in the first year, full data in the second year, and partial data in the third year. Lastly, we also have a Measures table.
I also slightly modified this dataset. I brought in a Holiday table and linked it to the Extended date table so that it can properly calculate the holidays we will remove in this analysis. If you’re not sure how to do this, check out this tutorial.
Then I filtered out the first partial month in the dataset because that month only had a few days of data and didn’t have the business days that we need. Take note that we’re starting with the first full month; but other than that, this will be your standard dataset. So let’s jump into power query and start working on this Power BI tip.
If we take a look at our data, you can see we have different orders on a particular day. The goal is to number everything that starts with April 1st as our first business day, April 2nd as our second business day, April 3rd as our third business day, and so on. We’ll number this from 1 to N business days for each month while taking out the weekends and holidays.
Creating A Duplicate Table
To start things off, we need to create a duplicate of this table so we can aggregate into Total Sales and bring it into the individual observation level. We’re going to call this duplicate table Sales Aggregated.
You may have noticed, we duplicated the Sales table rather than just referencing it. In this case, we’re going to join this table with the original Sales table. If we reference it, it will cause problems when we join the two.
The first thing to do is use a Group By on the OrderDate column. Click on the Advanced button because it will give us a full picture of what we’re doing here. We will put Total Day Sales as our new column name, and it will be the sum of our line total.
Now we have each date (first column) and total sales for that day (second column).
Merging The Dates Table
The next step in this Power BI tip is to merge this with our Dates table to figure out which of those days are business days and which of them are not. We can Group By again, split those out, and number them appropriately.
We will merge our Dates table and link the OrderDate to Date. We can see at the bottom part that the selection matches every row of the table, as it should.
The field we’re primarily interested in is IsBusinessDay.
This is the primary field that we’ll be grouping on, and then use the Month & Year and MonthnYear fields.
We also need to check on DayofWeekName to make sure that we’re filtering out the proper days.
Once we click on the OK button, it will give us exactly what we need.
We also have to make sure that our Order Date is sorted in ascending order.
Then we go back and create a second Group By.
This time, we’ll click on the Advanced button, group on Month & Year, and then add a second grouping.
The second grouping is for IsBusinessDay so we can number the ones where business days are true.
Then we’ll create an AllRows function so it will not aggregate and we’ll be able to go back to the original level of granularity.
This should give us nested tables where we can see a table for true and a table for false for each month.
Creating A Custom Column
Next, we need to create a custom column to add the count that we need for the business days.
We’re going to use a function called Table.AddIndexColumn, which will operate on the AllRows table, and we’ll call it Day Index. We want the index to start with 1, and increment by 1 with each new business day.
Then we can remove the first three columns, and expand on our fourth custom column.
If we look at the result, it gives us the day index, which counts the business days (true), and then counts the days that are weekends and holidays in each month (false). Next, we have to check that these are in their proper field type.
Let’s go back to our Sales table. We’ll merge the Aggregate table that we just created with our Sales table.
We’ll merge those on the OrderDate. You can see at the bottom that it matches all the rows from the first table.
When we expand the Sales Agg column, select the new fields that we just added: the Month & Year, DayOfWeekName, MonthnYear, IsBusinessDay, and DayIndex columns.
Let’s also clean up our data before we click on Close & Apply. Let’s move the Sales Agg table in our Data Prep section, then unload that table since we’re not going to need it within the data model. Then click on Close & Apply.
Our power query prep work is now done.
Creating A What-If Parameter
Remember that in the original task we want to vary the number of days that we look at from 5 to 10 and 15 to 20. I think the best way to do this is with a new what-if parameter.
We’ll call the what-if parameter First N Business Days and its data type will be a whole number. The minimum is set to an increment of 1 and a maximum of 20. Then we’ll default to 5, which is the first value that the user wanted.
We now have a slicer that automatically creates the harvest measure to capture the slicer value.
Let’s create a table with our results. Let’s put our Month & Year dimension in the canvas and then sort it by Month & Year.
Once we have this field properly sorted, we can just drop the Total Sales measure and expand it out.
Creating A Measure For Number Of Business Days
We need to create one more measure that takes a look at the value of the slider parameter and only calculates for the number of business days.
We’ll call this new measure Total Sales N Bus Days. We’ll start with the CALCULATE function since we’re definitely changing context, and with our Total Sales measure. We’re going to filter this with the ALLSELECTED of our Sales table.
The next step is to write the conditions that we want to take into account for the slider. We have our Sales Day Index that we created in Power Query, and we want this to be less than or equal to the First N Business Days value, which is the harvested value of the slicer.
For example, if the slider is at 5, we want all the days of the Sales table where the day index is less than or equal to 5.
And then we want to take out the non-business days. We’ll reference the Date table and use the IsBusinessDay field. We’ll only look at the result that is TRUE.
Lastly, bring the context back for Month & Year and close out the measure. We should be good to go at this point.
Let’s drop the new measure into our table.
We can take the slider slicer to the 10 day-mark, and the table will dynamically calculate at the 10-day mark.
***** Related Links *****
Showcasing Workday Number in Power BI Using DAX
Adding Workday And Weekend Numbers Into Your Date Table
Calculate Workdays Between Two Dates In Power BI
Conclusion
In this Power BI tip, we talked about how to do a like-for-like comparison dynamically using a Power Query solution. We can put this into a bar chart or a line chart.
In some of our upcoming tutorials, we’ll take a look at the DAX solution and implement the visualization of the KPI scroller.
If you enjoyed the content covered in this particular tutorial, please don’t forget to subscribe to the Enterprise DNA TV channel.
Brian
