count number of days depending on date range in filter

[emmaclarke83]

Hi I am trying to do some absence calculations.  I have a start and end date for each occasion of absence.  If I have a date slicer and select a date range, I would like to count the number of days absence within that date range filter, excluding weekends

For example, if someone is absent between 18/09/2017 and 13/10/2017 that is 20 working days (excluding weekends).  If I apply a filter between 09/10/2017 and 15/10/2017, I want to count the absence days so for the above example it would show 5 working days. Any idea on the Measure syntax for this?

Thanks

[v-qiuyu-msft · Accepted solution]

Hi @emmaclarke83,

You can create a calendar table firstly, drag the date from calendar table in slicer, then create a measure below: 

Diff =
var start_Date=FIRSTDATE(ALLSELECTED('CALENDAR'[Date]))
var end_Date=LASTDATE(ALLSELECTED('CALENDAR'[Date]))
var current_Start=MAX('Table1'[StartDate])
var current_end=MAX('Table1'[EndDate])
return
IF(current_end>=start_Date&&current_Start<=end_Date,DATEDIFF(MAX(start_Date,current_Start),MIN(end_Date,current_end),DAY)-COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(MAX(start_Date,current_Start),MIN(end_Date,current_end)),"DoW",WEEKDAY([Date],1)),[DoW]=1||[DoW]=7))+1)

For details, you can see attached pbix file. 

Best Regards,
Qiuyun Yu 

[emmaclarke83]

Hi Qiuyun Yu 

I have just noticed an issue with this measure.  It does doesn't SUM.  Is there a way round this?   I need to be able to sum up total number of days from this measure

Thanks

[emmaclarke83]

That's perfect, thank you 🙂