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

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 37 members 947 guests
Recent signins: