Date filter on a date range?

[Kurren]

I have a fact table with two columns, "start date" and "end date". I want a date range filter slider in my report, but I want the filter to select all rows where the start and end dates fall within the slider.

Eg if we have the following rows:

ID	Start date	End date
1	1 Mar 2020	1 Oct 2020
2	1 Aug 2020	1 Apr 2021
3	1 Aug 2019	1 Mar 2020

and the date filter starts at "1 Jan 2020" and ends at "31 Dec 2020", then only row 1 should pass the filter and be shown on the report, as the start date and end date fall within the range.

Is this possible?

[Anonymous]

Hi @Kurren,

According to your description, it seems like a common multiple date range analysis requirement. I'd like to suggest you take a look at following link start date, end date part if it suitable for your requirement:

Before You Post, Read This 

Regards,

Xiaoxin Sheng

[FreemanZ · Accepted solution]

Hi @Kurren 

It is normal operation. but please ensure to have a independent date table for the slicer/slider.

The measure shall look like this:

DurationSum :=

VAR _min = MIN(DateTable[Date])

VAR _max = MAX(DateTable[Date])

RETURN 

CALCULATE(

    SUM(TableName[Value]),

    TableName[StartDate]<=_min

        &&TableName[EndDate]<=_max

)

just try and let us know. 

[Kurren]

Thanks. Only difference is I had to REMOVEFILTERS the existing date filter within the CALCULATE function.