# Compare data by working/billable days not calendar days

Hey everyone,

Broad question here. I am trying to think through setup for this problem:

Executive team tracks current month performance by looking at prior month performance “to date” with the caveat that they do not compare the first 5 calendar days but the first 5 billable days. They again do this at day 10, day 15, and day 20. The team does not work weekends.

So a way with a date table to calculate running total of business days for each month (@Melissa I think you’re my only hope - lol) For example:

July 2021 the 5th billable day of the month is really the 7th of July
August 2021 the 5th billable day of the month is really the 6th of August
November 2021 the 5th billable day of the month is the 5th of November

So I’m trying to think through the best way to setup my date table or maybe a different table to make a slicer that would show a comparing value for July 7th and August 6th when you click on a number of business days slicer set to 5, 10, 15, 20. I have a sneaking suspicion that the day count could change so I’d like to set it up to be a little flexible.

Anyway would love to hear some of the pros thoughts on this setup question.

Thanks!

Hi @HennemanTJ.

For example, see the [IsWorkingDay], [IsBusinessDay], [Day Type] columns in @Melissa’s Extended Date Table:

Hope this helps.
Greg

Hey Greg,

Thanks for the reply! First thing I do with every powerbi file I make is add Melissa’s date table, I love it.

The trick for me on this one is having running totals for each month that can be slicers based on month. Maybe I’m just over thinking it and need to just dig in…

Thanks!

1 Like

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

Definitely, in part. The piece that I’m still trying to think through is how to get just the first n business days/working days of the month for each month for comparison. Would love any insight anyone can offer.

This is quite an interesting DAX question that I hadn’t come across in exactly this form before. The approach I took was to create a what-if parameter for the first N business days of the month, and then use that parameter as the first value in a TOPN-based table variable to filter the dates table based on the first N business days of the month, using the IsBusinessDay field in the Extended Date Table.

Here’s the measure that does the heavy lifting:

``````First N Business Days Sales =

VAR BusDayTable =
FILTER(
Dates,
)

VAR FiltBusDayTable =
CALCULATETABLE(
TOPN( [First  N Business Days Value], BusDayTable, Dates[Date], ASC ),
ALLEXCEPT( Dates, Dates[Month & Year] )
)

VAR FiltSum =
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED( Dates ),
Dates[Date] IN SELECTCOLUMNS( FiltBusDayTable, "@Date", Dates[Date] )
)
)

RETURN FiltSum
``````

and here’s what it looks like all put together:

I hope this is helpful. Full solution file attached below.

2 Likes

Yes! This is perfect. Thanks a bunch @BrianJ!

Great! Glad that worked well for you.

Thanks for the interesting problem - I may actually do a video on this one, since I think it’s a pretty interesting and generalizable use case.

• Brian
1 Like

That’s why I reached out on the forum, I know there are a few business applications for a service based business that doesn’t work a few days of the week.

I know a lot of “hours for dollars” business models like comparing this month to last years but sometimes last years month only had 19 working days instead of 21, which could skew metrics 10-15%. I think a number of these groups look at metrics on a weekly basis but I think this could be a better solution because you can introduce holidays as a T/F for billable days and still see N billable days slicer for your KPIs.

Thanks again @BrianJ!

2 Likes

That’s one of the reasons that @Melissa 's extended date table is such a marvel. It would be an almost trivial matter to set up a slicer to switch the calculation between [IsWorkingDay] and [IsBusinessDay] depending on whether you wanted to include holidays or not.

The construct would be pretty powerful - the N business days parameter slider, combined with a slicer to select between calender days, non-weekend days only, and non-weekend, non-holiday days. I think I’ve got me a video structure now…

• Brian
2 Likes

FYI - I’ve kind of fallen in love with this question, which is both a very useful like-for-like comparison pattern as well as having a lot of interesting technical aspects. In addition to the solution I provided above, I’ve built out what I think is a good Power Query solution with a bit of straightforward DAX at the end, and @AntrikshSharma has developed a brilliantly efficient full DAX solution. Attached is the PBIX that contains both the PQ and DAX solutions, both of which I think are better than the original solution I provided to you above.

I’m actually exploring all these concepts in a 3-part YouTube miniseries I expect to finish out tonight and we will post over the next couple of weeks. Part one is the PQ solution, part two is the DAX solution plus some tricks about dynamic visual titles, and part 3 explores some intesting visualization techniques for thisi type of data.

Thanks again for providing such a great question!

3 Likes

@BrianJ Yes, this question appealed to me, too. I was looking at something similar when you posted on YouTube, had some time on my hands and decided to give it a bash…

1 Like

Here’s the first video in the series stemming from @HennemanTJ 's original question.

– Brian

Well done, sir! This is very similar to the solution @AntrikshSharma provided that I will be working through in video #2. As you identified as well, GENERATE() is the key to the most efficient solution on this one.

Very nice piece of DAX coding.

– Brian

2 Likes

Yes, I like @AntrikshSharma stuff and was very pleased when I saw that he also decided to go down the GENERATE route with his solution.

3 Likes

@Craig Amazing solution, you are now taking your DAX knowledge to the next level!!

3 Likes