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

[emmaclarke83]

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

[v-caliao-msft]

@emmaclarke83,

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

[emmaclarke83 · Accepted solution]

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]))
)
)