Measure time in days excluding weekends

[rogerdea]

I have a measure which counts the number of days between two dates using the DATEDIFF funtion and that's fine.  However, as this is a business related measure, i need to exclude weekends.

So Friday to Monday should be calculated as 1 day.

Any ideas if this is possible?

Thanks

[Anonymous]

Create a proper Dates table and then you'll be able to do this:

[# Working Days] =
CALCULATE(
	COUNTROWS( Dates ),
	KEEPFILTERS( Dates[Working Day] = TRUE() )
)

Best

Darek

[rogerdea]

Thanks for the reply.  I now have this calculation in my date table, but how do i use this to measure working days from a date in my other data table?  Sorry if i have misunderstoof Darek.

[Anonymous]

Very easy in fact...

If you have 2 dates D1 and D2, then the number of working days between them is this:

var __dateStart = D1 -- your start date 
var _dateEnd = D2 -- your end date
var __workingDays =
	calculate(
		[# Working Days],
		Dates[Date] >= D1,
		Dates[Date] <= D2
		ALL( Dates )
	)
return
	__workingDays

Best

Darek

[HotChilli]

You need the equivalent to Excel's NETWORKDAYS

https://www.sqlbi.com/articles/counting-working-days-in-dax/