Forum Discussion
Post PartisanHow to return only records for a date between two other dates?
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 |
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.
4 Replies
- AmiraBedh
Super User
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]) ) )- 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 )
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.
