cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.
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.
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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 94 members 1,498 guests
Please welcome our newest community members: