Flag if the date selected in the slicer is between the start and end dates

[kai1109]

I'm sorry if it's hard to see because I type with the translation function

Project	Task	Startdate	Endate
A	e	2023/4/27	2023/4/29
A	f	2023/5/1	2023/5/4
A	g	2023/5/5	2023/5/7
B	e	2023/4/28	2023/4/29
B	f	2023/5/2	2023/5/4
B	g	2023/5/3	2023/5/7

Here's a sample of the data above

When selecting 2023/5/6 with slicer

I want to flag the third and sixth lines as shown in the image below.

 

When selecting 2023/5/6, the ideal shape is the table below.
Somebody help me, please

Projectname	taskname
A	g
B	g

 

Please let me know if there is any way to do both

【formula someone was trying to calculate】

value =

VAR SelectedDate = SELECTEDVALUE('calender'[Date])

RETURN

IF(

SelectedDate <> BLANK() &&

COUNTROWS(

FILTER(

'test',

'test'[StartDate] <= SelectedDate &&

'test'[Enddate] >= SelectedDate

)

) > 0,

1,

0

)

[kai1109]

Thank you.It's solved!

[v-rongtiep-msft · Accepted solution]

Hi @kai1109 ,

Please have a try.

1. Create another calendar table without relationship with the table.
2. Then put the calendar date into the slicer.
3. Create a measure.

Measure =
VAR _1 =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF (
        _1 >= SELECTEDVALUE ( 'Project Task Startdate EndateTable'[Startdate] )
            && _1 <= SELECTEDVALUE ( 'Project Task Startdate EndateTable'[Endate] ),
        1,
        0
    )

 4.Then filter the table.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.