Forum Discussion
How to dynamically limit a Between Date Slicer.
Hi everyone,
I have a Forecast Issue Date dropdown slicer and a Forecast Date Between slicer.
For example, if I select 15 Sep as the Forecast Issue Date, the Forecast Date slicer should show only:
15, 16, 17, 18, 19, 20 Sep (selected date + next 5 days).
Currently, the Forecast Date slicer shows all dates available in the table.
How can I dynamically limit the Between slicer based on the selected Forecast Issue Date?
Any help would be appreciated. Thanks!
Create a measure like
Forecast Date Between Is Visible =
VAR ForecastDate =
SELECTEDVALUE ( 'Forecast Date'[Date] )
VAR CurrentBetweenDate =
SELECTEDVALUE ( 'Forecast Date Between'[Date] )
VAR Result =
IF (
CurrentBetweenDate >= ForecastDate && CurrentBetweenDate <= ForecastDate + 5,
1
)
RETURN
Result
And use that as a filter on the between slicer, set to show only when the value is 1.
5 Replies
v-abhinavmuCommunity Support
Hi SumitB143,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.Thank you.
- ShivekMaharaj
Community Champion
Hi SumitB143,
I would separate two requirements here:
- limit the actual report data to Forecast Issue Date through Forecast Issue Date + 5 days
- physically change the minimum/maximum range shown by the Between slicer
The first one is straightforward with DAX.For example:
Forecast Date In Range = VAR IssueDate = SELECTEDVALUE ( 'Forecast'[Forecast Issue Date] ) VAR CurrentDate = MAX ( 'Date'[Date] ) RETURN IF ( NOT ISBLANK ( IssueDate ) && CurrentDate >= IssueDate && CurrentDate <= IssueDate + 5, 1, 0 )You can then apply Forecast Date In Range = 1 as a visual-level filter to the visuals that should respect that window.
Microsoft documents that slicers normally filter other slicers and visuals, and measures can be used in visual-level filters.
However, I would be careful with the second requirement.
A Between date slicer is a range control based on the full date column, and Power BI does not provide a native setting that dynamically rewrites its min/max bounds from another slicer selection.
So if you specifically need the second slicer to only display:
15 Sep 16 Sep 17 Sep 18 Sep 19 Sep 20 SepI would consider using a list/dropdown date slicer instead of Between. The first slicer can then filter the available date values more naturally.
Microsoft’s slicer documentation explains that slicers filter other slicers by default, while the date slicer guidance documents the specific behavior of Between and Date Picker styles.
So my preference would be:
Forecast Issue Date -> defines the valid 6-day window Forecast Date list/dropdown slicer -> shows only dates in that windowrather than trying to force the Between slicer itself to dynamically recalculate its physical range.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
- v-abhinavmu
Hi SumitB143,
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
johnt75Super User
Create a measure like
Forecast Date Between Is Visible =
VAR ForecastDate =
SELECTEDVALUE ( 'Forecast Date'[Date] )
VAR CurrentBetweenDate =
SELECTEDVALUE ( 'Forecast Date Between'[Date] )
VAR Result =
IF (
CurrentBetweenDate >= ForecastDate && CurrentBetweenDate <= ForecastDate + 5,
1
)
RETURN
Result
And use that as a filter on the between slicer, set to show only when the value is 1.
- Ritaf1983
Hi SumitB143
If Forecast Issue Date and Forecast Date are two separate columns, you can use the selected Issue Date directly and filter the Forecast Date slicer accordingly.
Fixed 5-day range
First create a measure:
Forecast Date Filter = VAR IssueDate = SELECTEDVALUE ( 'Forecast'[Forecast Issue Date] ) VAR CurrentForecastDate = MAX ( 'Forecast'[Forecast Date] ) RETURN IF ( NOT ISBLANK ( IssueDate ) && CurrentForecastDate >= IssueDate && CurrentForecastDate <= IssueDate + 5, 1, 0 )
Then select the Forecast Date Between slicer, add Forecast Date Filter under Filters on this visual, and filter it to 1.
So if the selected Forecast Issue Date is 15 Sep, the Forecast Date slicer will be limited to:
15 Sep – 20 Sep
If the number of days should also be dynamic
Power BI has Numeric range parameters, which are useful when you want the user to control a numeric value such as the number of forecast days.
Go to:
Modeling > New parameter > Numeric range
For example:
- Name: Forecast Days Ahead
- Data type: Whole number
- Minimum: 1
- Maximum: 30
- Increment: 1
- Default: 5
Power BI creates a small disconnected parameter table and a measure containing the currently selected parameter value.
If you choose Add slicer to this page, the user can select the required number of days directly from the report.
You can then use the generated parameter measure in the same logic:
Forecast Date Filter = VAR IssueDate = SELECTEDVALUE ( 'Forecast'[Forecast Issue Date] ) VAR DaysAhead = [Forecast Days Ahead Value] VAR CurrentForecastDate = MAX ( 'Forecast'[Forecast Date] ) RETURN IF ( NOT ISBLANK ( IssueDate ) && CurrentForecastDate >= IssueDate && CurrentForecastDate <= IssueDate + DaysAhead, 1, 0 )
Again, apply:
Forecast Date Filter = 1
as a visual-level filter on the Forecast Date slicer.
This makes the range dependent on two user selections:
Forecast Issue Date = 15 Sep
Forecast Days Ahead = 5→ Forecast Date range: 15 Sep – 20 Sep
The parameter table does not need a relationship to the model. Its purpose is simply to provide the selected numeric value to the measure.
If the requirement is exactly 6 dates including the selected date, then DaysAhead = 5 is correct because the starting date itself is also included.
