Display the result of a table for a period whose table contains a start date and an end date.

[efhache]

I have a table that contains an id, a start date, an end date:

ID	Start Date	End date
12345	01/02/2021	07/02/2021
67890	01/02/2021	07/02/2021
98765	08/02/2021	14/02/2021

I would like to filter and display the rows for which the period I select in the filter is included in the space between start date and end date. So, for example If the filter is set on period from 01/02 to 07/02, I'll get the lines for IDs 12345 and 67890 ; if the filter is set in period 08/02 to 14/02, I'll get the line for ID 98765; If the filter is set on a period from 02/02 to 05/02 , I'll get also the lines for IDs 12345 and 67890...

I know that I can use slicer to select a date period, but it will filter only on one date column and not select the corresponding period...

Can you help me to get what I want

[lbendlin · Accepted solution]

First create a calendar table that covers your date range. This is a calculated table, or a data source.

Table 4 = CALENDAR(min('Table (2)'[Start Date]),max('Table (2)'[End date]))

Use that table to feed your slicer.

Then create a measure to decide if you want to show a row or not

Visible := 
var a=calendar(min('Table'[Start Date]),max('Table'[End date]))
var b=CALENDAR(min('Table 4'[Date]),max('Table 4'[Date]))
var c=INTERSECT(a,b)
return if(COUNTROWS(c)>0,1,0)

and lastly add that measure as a filter to your visual and set it to "Visible is 1"

[efhache]

Wonderful, I hadn't thought about the measures at all. I feel a bit silly.

Many thanks