Count of Months with X Business Days

[Jkaelin]

Good morning,

I am struggling on counting the months that have X amount of business days.  Most months have approximately 19-23 business days.  I need a measure that can count how many months, in my calendar table, have 22 business days.  I have a boolean ID for "Business Days" & a standard calendar table with all the related bells & whistles.  Just can't figure out this calculation.

For example, for 2017, January & May both had 22 business days.  I want a measure that would return, "2" for 2 months with 22 business days.  

My live attempts are way off, so I don't think posting my incorrect DAX measures would be helpful.  Is this enough information?

Thank you,

James

[fhill]

... What about national holidays?  Are you going to try to exclude these as well?

[fhill]

Here's a count of all Mondays-Fridays per month:

Measure = CALCULATE(COUNT(DimDate[Date]), FILTER( DimDate , WEEKDAY(DimDate[Date]) >=2 && WEEKDAY(DimDate[Date]) <= 6))

[Jkaelin]

@fhill

Thank you for the quick response.  The formula is good.  It provides the number of working days per month.  I am seeking the number of months with X amount of business days.  So the results you provided, Can a measure calculate how many months were 21 working days, or 20 working days, etc??

Thank you & kindly,

James

[fhill · Accepted solution]

.... there's probably a better way of doing this, but here's one option:

I've added this Calculated Column to the Date Table:  (This creates lots of duplciates we will clean up in the next step.)  You'll probably COUNTA your binary column?

WorkDays_Per_Month = CALCULATE(COUNT(DimDate[Date]), FILTER(ALLSELECTED(DimDate), DimDate[Year] = EARLIER(DimDate[Year]) && DimDate[Month] = EARLIER(DimDate[Month]) && WEEKDAY(DimDate[Date]) >=2 && WEEKDAY(DimDate[Date]) <= 6))

Next we need a Month Table.  You can use Excel to quickly paste the first of each month, or here's a Query to calculate the first and last day of each month 100 months backward and forward.

let
Source = Table.FromList({-100..100}, each{_}),
AddedStartOfMonth = Table.AddColumn(Source, "StartOfMonth", each Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),[Column1])), type date),
AddedEndOfMonth = Table.AddColumn(AddedStartOfMonth, "EndOfMonth", each Date.EndOfMonth([StartOfMonth]), type date),
RemovedColumn = Table.RemoveColumns(AddedEndOfMonth,{"Column1"})
in
RemovedColumn

I've created a 1:1 Relatoinship between the Start of each month and the Date Table.  This Calculated Column now on the Month table brings over the number of workdays per month (only 1 value per month, unlike the multiple values on the date table).

First_Of_Month_Only = RELATED(DimDate[WorkDays_Per_Month])

Lastly, I created a custom Table with just the values '18,19,20,21,22,23,24'

I added another realtionship between these numbers and the First_Of_Month_Only column as a 1:Many.  Now I can COUNT FirstOfMonthOnly as a coulmn.  (I"m sure there's a betterw way to do this with more complicated DAX...?  If you are looking for Measures, maybe just COUNT the number of times "20" happends via a Filter and you have a different Measure for 20,21,22,23 that you can stack together as values in a graph?)

[Jkaelin]

@fhill

Thank you very much for the detailed response.  I didn't realize I asked such a technical question, so I appreciate the solid feedback.  I'm studying your response this afternoon.

Thanks.

[KHorseman]

OK, I'm making some assumptions here. First, assuming your date table has an explicit Month column, which it should anyway. Second, assuming both the weekday filter @fhill used is necessary in addition to the Working Days column you mentioned. SoI guess I'm assuming that Working Days is true for every day that isn't a holiday, and that we're using the weekday number to filter out weekends.

Measure = COUNTROWS(
	FILTER(
		ADDCOLUMNS(
			VALUES(DimDate[Month]),
			"DayCount", CALCULATE(
				COUNT(DimDate[Date]),
				FILTER(
					DimDate,
					WEEKDAY(DimDate[Date]) >=2 && 
					WEEKDAY(DimDate[Date]) <= 6 &&
					DimDate[Working Days] = TRUE
				)
			)
		),
		[DayCount] >= 20
	)
)

Also assuming the number of days we care about is 20. If you want to be able to input an arbitrary number of working days...we can talk about that.

[KHorseman]

Does your date table have a Month column?

[Jkaelin]

I already have a holiday table that is incorporated into the master calendar table.  So the boolean column of "Working Days" accounts for that.  I just can't create a measure that calculates number of months with X amount of working days.  😞