Forum Discussion
How can I create a date range filter on a visual that works logically with two dates in a table?
1. I want to be able to filter a visual of schedules that fall into a range of specified dates. Here's some sample data:
| 1. Underlying Data | ||
| Start Date | End Date | |
| Schedule1 | 11/21/2022 | 11/27/2022 |
| Schedule2 | 12/5/2022 | 12/25/2023 |
| Schedule3 | 1/2/2023 | 1/15/2023 |
2. I specify the range of dates which can be selected from a calendar or input manually:
| 2. Data Filter(s) | |||||
| Date From | 10/1/2022 | select from calendar or input manually | |||
| Date To | 12/31/2022 | select from calendar or input manually |
3. This is the logic that I use to identify the appropriate schedules:
| 3. Logic: | Start Date is <= Date To and |
| End Date is >= Date From |
4. This would be the resulting visual if successfully filtered:
| 4. Resulting Visual | ||
| Start Date | End Date | |
| Schedule1 | 11/21/2022 | 11/27/2022 |
| Schedule2 | 12/5/2022 | 12/25/2023 |
The issue I have been experiencing is that as soon as I apply the first date then the filters are applied. I have a workaround where the logic is applied in Power Query to create defined views of Today, This Week, This Month and This Quarter and they're bookmarked accordingly. However it's not very flexible. Thanks in advanve for your help.
Hi , Anonymous
According to your description, you want to "create a date range filter on a visual that works logically with two dates in a table".
Here are the steps you can refer to :
(1)My test data is the same as yours. We need to click "New Table" to create a table as slicer:
Date = CALENDAR(DATE(2021,1,1),DATE(2023,12,31))(2)We can create a measure:
Measure = var _current_start=SELECTEDVALUE('Table'[Start Date]) var _current_end =SELECTEDVALUE('Table'[End Date]) var _slicer_min = MIN('Date'[Date]) var _slicer_max=MAX('Date'[Date]) return IF(_current_start<=_slicer_max && _current_end>= _slicer_min ,1,0)(3)Then we can put the 'Date'[Date] on the slicer and the field we need on the table visual and configure the measure in the "Filter on this visual" and we will meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
FreemanZSuper User
Suppoing your table named Data, try to create a new table with the code below:Table =VAR DataFrom = DATE ( 2022, 10, 1)VAR DataTo = DATE( 2022, 12, 31)RETURNCALCULATETABLE(Data,Data[Date Start] <= Datato,Data[Date End] >= DataFrom)Thanks for your reply. How can I get this to work with a contiguous Calendar table in order to make dates from and to dynamic? See my effort in the attached photos. For example If I filter Jan 9th 2023 to Dec 31st 2023 then Schedule 3 should appear however it doesn't. I think my relationship between Calendar Date and Date Start is wrong. Your help is greatly appreciated.
- v-yueyunzh-msft
Community Support
Hi , Anonymous
According to your description, you want to "create a date range filter on a visual that works logically with two dates in a table".
Here are the steps you can refer to :
(1)My test data is the same as yours. We need to click "New Table" to create a table as slicer:
Date = CALENDAR(DATE(2021,1,1),DATE(2023,12,31))(2)We can create a measure:
Measure = var _current_start=SELECTEDVALUE('Table'[Start Date]) var _current_end =SELECTEDVALUE('Table'[End Date]) var _slicer_min = MIN('Date'[Date]) var _slicer_max=MAX('Date'[Date]) return IF(_current_start<=_slicer_max && _current_end>= _slicer_min ,1,0)(3)Then we can put the 'Date'[Date] on the slicer and the field we need on the table visual and configure the measure in the "Filter on this visual" and we will meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly