cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
emmaclarke83 Regular Visitor
Regular Visitor

count number of days depending on date range in filter

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: count number of days depending on date range in filter

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)

 

q3.PNG

 

For details, you can see attached pbix file. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Moderator v-qiuyu-msft
Moderator

Re: count number of days depending on date range in filter

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)

 

q3.PNG

 

For details, you can see attached pbix file. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

emmaclarke83 Regular Visitor
Regular Visitor

Re: count number of days depending on date range in filter

That's perfect, thank you Smiley Happy

emmaclarke83 Regular Visitor
Regular Visitor

Re: count number of days depending on date range in filter

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 37 members 947 guests
Please welcome our newest community members: