Show all records that touch a date range

[thmonte]

I am trying to create a measure to use in my visuals that will filter all dates that cross a range.

So I have one table that has start date & end date columns.  I also have a Date Table with unique dates in a range with a relationship.  I pull in my Unique Date Slicer and I write a measure against the Data Table that looks similar to this.

FILTER (
'DataTable',
( 'DataTable.StartDate <= dateSlicer.minDate
&& DataTable.EndDate >= dateSlicer.maxDate )
)

And then put visual level filter with that measure thats greater than 0

This works but in my table I don't get the results I want unless i filter down to a single day.

so if I have a record with a start Date and end Date of 8/11 a single day it will not show up in my main table until I filter down to that one day.

Here is a Sample PBIX file - https://drive.google.com/open?id=1mBnO6FtAQ00iRmaFKbImLEV_DR_u72WW

[Anonymous]

@thmonte

your filter function is wrong. you were looking at < min date and more than max date. you needed to look at between the 2 dates

Count Rows =
VAR maxDate =
MAX ( 'Date'[Date] )
VAR minDate =
MIN ('Date'[Date])
RETURN
CALCULATE (
COUNTROWS ( Records ),
FILTER (
Records,
( Records[Start Date].[Date] >= minDate
&& Records[Fake End Date].[Date] <= maxDate )
)
)

[TomMartens · Accepted solution]

Hey,

the problem that you have to tackle is called "events in progress", here you will find some readings that also provide solutions.

• https://blog.crossjoin.co.uk/2013/06/13/a-new-events-in-progress-dax-pattern/
• http://sqljason.com/2012/11/classifying-and-solving-events-in.html
• https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

and a whitepaper that describes the problem in much detail

• http://sqljason.com/2012/11/classifying-and-solving-events-in.html

Regards,

Tom