cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
emmaclarke83 Regular Visitor
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
emmaclarke83 Regular Visitor
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]))
)
)

View solution in original post

2 REPLIES 2
Moderator v-caliao-msft
Moderator

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

@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 Regular Visitor
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]))
)
)

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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