topic Measure time in days excluding weekends in DAX Commands and Tips

Measure time in days excluding weekends

rogerdea — Fri, 12 Jul 2019 06:15:30 GMT

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

Re: Measure time in days excluding weekends

HotChilli — Fri, 12 Jul 2019 08:37:43 GMT

You need the equivalent to Excel's NETWORKDAYS

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

Re: Measure time in days excluding weekends

Anonymous — Fri, 12 Jul 2019 09:37:05 GMT

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

Re: Measure time in days excluding weekends

rogerdea — Mon, 15 Jul 2019 20:12:38 GMT

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.

Re: Measure time in days excluding weekends

Anonymous — Mon, 15 Jul 2019 20:32:29 GMT

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