Forum Discussion
Filtering When Record Open/Closed Dates Intersect With A Slicer Date Range
- 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
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
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.