Forum Discussion
Helper IIDate filter on a date range?
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?
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.
3 Replies
FreemanZSuper User
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.
KurrenThanks. Only difference is I had to REMOVEFILTERS the existing date filter within the CALCULATE function.
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:
Regards,
Xiaoxin Sheng
