Forum Discussion
Using date slicer on a measure
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.
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.
3 Replies
Use power pivot - create a measure which filters based on start and end date.
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.Thank you very much v-jingzhang.
