How to return only records for a date between two other dates?

[ERing]

I have data like below where each work order has an Start_Date, In_Progress_Date, and End_Date. My requirement is a report where there is a slicer for the Start_Date and  slicer for the End_Date and the table visual shows only records with an In_Progress_Date between the Start_Date and End_Date.

In the example where the user selects a Start_Date of 09/30/23 and an End_Date of 11/01/23, work orders 9 & 10 would be excluded because the In_Progress_Date falls on or before the Start_Date or on or after the End_Date.

Work_Order_ID	Start_Date	In_Progress_Date	End_Date
1	9/30/2023	10/1/2023	11/1/2023
2	9/30/2023	10/2/2023	11/1/2023
3	9/30/2023	10/3/2023	11/1/2023
4	9/30/2023	10/4/2023	11/1/2023
5	9/30/2023	10/5/2023	11/1/2023
6	9/30/2023	10/6/2023	11/1/2023
7	9/30/2023	10/7/2023	11/1/2023
8	9/30/2023	10/8/2023	11/1/2023
9	9/30/2023	9/30/2023	11/1/2023
10	9/30/2023	11/1/2023	11/1/2023

[v-xuxinyi-msft · Accepted solution]

Hi @ERing

I ran the following test.

1. Create two slicer table

2. Create a measure as follows

Measure = 
IF( 
    SELECTEDVALUE('Table'[In_Progress_Date]) > SELECTEDVALUE(startdate[Date]) 
    && SELECTEDVALUE( 'Table'[In_Progress_Date]) < SELECTEDVALUE(endstart[Date]),
    1,
    0
)

Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

Is this the result you expect?

Best Regards,

Community Support Team _Yuliax

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

[AmiraBedh]

Try the following : 

Filtered Work Orders = 
CALCULATE(
    COUNTROWS('YourTableName'),
    FILTER(
        'YourTableName',
        'YourTableName'[In_Progress_Date] > MAX('YourTableName'[Start_Date]) &&
        'YourTableName'[In_Progress_Date] < MIN('YourTableName'[End_Date])
    )
)

[ERing]

Hi @AmiraBedh I'm not looking to return a count. I need to be able to show a table visual with the results. In this case I want my table visual to exclude the last two rows. 

[AmiraBedh]

A calculated column maybe ?

Is Within Date Range = 
IF(
    AND(
        'YourTableName'[In_Progress_Date] > SELECTEDVALUE('YourTableName'[Start_Date]),
        'YourTableName'[In_Progress_Date] < SELECTEDVALUE('YourTableName'[End_Date])
    ),
    1,
    0
)