cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## date slicer to filter 2 date columns and also if one date column is blank

Hi, I am trying to calculate the number of occasions of absence.  My table has 2 columns, start date and end date.  I also have a date table.  I would like to add a date range slicer to filter.  The criteria is if the start date >= the start date on the slicer and if the (end date <= the end date on the slicer or if the end date is blank)

Can someone help with the syntax please? thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: date slicer to filter 2 date columns and also if one date column is blank

Hi Charlie

THanks for this.  Your solution didn't work correctly for me but I found a solution using the following

Count of Absence7 =
CALCULATE(
DISTINCTCOUNT('AbsenceViewOccasions'[index]),
FILTER(
'AbsenceViewOccasions',
'AbsenceViewOccasions'[Start_Date] <= LASTDATE(Dates[Date]) &&
('AbsenceViewOccasions'[End_Date] >= FIRSTDATE(dates[Date]) || ISBLANK('AbsenceViewOccasions'[End_Date]))
)
)

2 REPLIES 2
Moderator

## Re: date slicer to filter 2 date columns and also if one date column is blank

Please create a measure by using the expression below.

```Measure =
VAR selectedstartdate =
MIN ( 'Date'[Date] )
VAR selectedenddate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTA ( Table1[StartDate] ),
FILTER (
ALL ( Table1 ),
( Table1[StartDate] >= selectedstartdate
&& Table1[EndDate] <= selectedenddate )
|| ISBLANK ( Table1[EndDate] )
)
)```

Regards,

Charlie Liao

Regular Visitor

## Re: date slicer to filter 2 date columns and also if one date column is blank

Hi Charlie

THanks for this.  Your solution didn't work correctly for me but I found a solution using the following

Count of Absence7 =
CALCULATE(
DISTINCTCOUNT('AbsenceViewOccasions'[index]),
FILTER(
'AbsenceViewOccasions',
'AbsenceViewOccasions'[Start_Date] <= LASTDATE(Dates[Date]) &&
('AbsenceViewOccasions'[End_Date] >= FIRSTDATE(dates[Date]) || ISBLANK('AbsenceViewOccasions'[End_Date]))
)
)

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,852)