cancel
Showing results for
Did you mean:
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

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

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

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

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

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

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.
Regular Visitor

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

That's perfect, thank you

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

Announcements

#### Community Highlights

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 94 members 1,498 guests
Recent signins: