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 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…
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,
Dates[IsBusinessDay] = TRUE
)
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
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.
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…
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.
@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…
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.
