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. 

   Using measures and a date table, I am able to get either a table visualization to display properly or aggregate visualizations but not both. I've attached the version where the table visualization appears to be doing what I would like it to do.

 

Link to pbix file  <---I recreated a fake version of my actual problem (details on page)

 

 

Thank you kindly for taking a look.

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

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Super 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

     

    • BigJimSlade's avatar
      BigJimSlade
      Regular Visitor
      4 years ago

      This is wonderful! The measure "Count" that you created does exactly what I need it to do. I need to go back now and break apart each DAX function to fully understand what is going on.

       

      Thank you for the easy to understand explanation.

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