Using date slicer on a measure

[tariktik]

First post here for Power BI newbie.

I have a table with a "Start Date", "End Date" columns among many other columns and I created a column to compute the duration between those two dates.

On my report, I added a slicer with Style "Between" based on the "Start Date" column.  If I select a date range, how can I exclude the records where the "End Date" is beyond the selected date range?  To illustrate, let's say I have the following records:

Start Date	End Date	Duration
1/1/2023	1/10/2023	9 days
1/4/2023	1/8/2023	4 days
1/2/2023	1/12/2023	10 days
1/3/2023	1/9/2023	6 days

If I set my slicer (based on Start Date) to 1/1/2023 - 1/10/2023, how can I exclude the 3rd record since the record is technically still not complete during the time period?

I am expecting to get only the following records:

Start Date	End Date	Duration
1/1/2023	1/10/2023	9 days
1/4/2023	1/8/2023	4 days
1/3/2023	1/9/2023	6 days

Thanks in advance for the help.

[v-jingzhang · Accepted solution]

Hi @tariktik 

I recommend that you create a date table in the model to have continuous dates and create a relationship from this date table's "Date" column to the "Start Date" column in the original table. Use date table's "Date" column in your slicer. This could avoid a scenario that some dates do not exist in the "Start Date" column in the original table, which would make the filter result not accurate. 

Date = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))

Then you can create the following measure

Flag = IF(SELECTEDVALUE('Table'[End Date]) <= MAX('Date'[Date]), 1, 0)

Add this measure on the table visual as a filter and set it to show items when value is 1. This will exclude the records you don't want from the table visual. 

The sample file is attached at bottom. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[tariktik]

Thank you very much v-jingzhang.

[RichardOfSydney]

Use power pivot - create a measure which filters based on start and end date.