Forum Discussion

BigJimSlade's avatar
BigJimSlade
Regular Visitor
4 years ago
Solved

Filtering When Record Open/Closed Dates Intersect With A Slicer Date Range

   I have a scenario where the customer wants to be able to pick a date range and have any item that was open at any time during that date range flag and filter in all of their visualizations.     U...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Thank you for providing the sample data.

     

    You already mentioned the magic word - INTERSECT.  However in your case you want a filter for Overlap, not just for Intersect.  That means you have to do that in separate steps.

    - First identify if your item dates INTERSECT with the slicer date range.  Use FILTERS(Calendar[Date])  as the left table, and use CALENDAR(Deliveries[Delivery Start Date],Deliveries[Delivery End Date]) as the right table.  Note :  you need to do that for each row in the selected table visual

    - Then set the flag for each of these rows to yes (intersects) or no (doesn't intersect) and use that flag as a visual or page filter.

     

    My implementation goes a step further and looks at the problem from the perspective of the Grand total.

     

    Count = 
var d = SUMMARIZE(Deliveries,Deliveries[Delivery ID],"Show",IF(COUNTROWS(INTERSECT(FILTERS('Calendar'[Date]),CALENDAR(min(Deliveries[Delivery Start Date]),max(Deliveries[Delivery End Date]))))>0,1,0))
return sumx(d,[Show])

     

    PBIX attached

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

Thank you for providing the sample data.

 

You already mentioned the magic word - INTERSECT.  However in your case you want a filter for Overlap, not just for Intersect.  That means you have to do that in separate steps.

- First identify if your item dates INTERSECT with the slicer date range.  Use FILTERS(Calendar[Date])  as the left table, and use CALENDAR(Deliveries[Delivery Start Date],Deliveries[Delivery End Date]) as the right table.  Note :  you need to do that for each row in the selected table visual

- Then set the flag for each of these rows to yes (intersects) or no (doesn't intersect) and use that flag as a visual or page filter.

 

My implementation goes a step further and looks at the problem from the perspective of the Grand total.

 

Count = 
var d = SUMMARIZE(Deliveries,Deliveries[Delivery ID],"Show",IF(COUNTROWS(INTERSECT(FILTERS('Calendar'[Date]),CALENDAR(min(Deliveries[Delivery Start Date]),max(Deliveries[Delivery End Date]))))>0,1,0))
return sumx(d,[Show])

 

PBIX attached

 

axellpadilla's avatar
axellpadilla
New Member
1 year ago

Just wanted to add a faster solution for future reference:

Active_Deliveries := 
VAR SelectedDates = VALUES('Calendar'[Date])
RETURN
CALCULATE(
    COUNTROWS(
        FILTER(
            Deliveries,
            VAR StartDate = Deliveries[Delivery Start Date]
            VAR EndDateRaw = Deliveries[Delivery End Date]
            VAR EndDate = IF(EndDateRaw < StartDate, StartDate, EndDateRaw)
            RETURN
            COUNTROWS(
                INTERSECT(
                    SelectedDates,
                    CALENDAR(
                        StartDate,
                        IF(
                            ISBLANK(EndDate) || EndDate = DATE(1900,01,01),
                            LASTDATE(SelectedDates),
                            EndDate
                        )
                    )
                )
            ) > 0
        )
    )
)