Forum Discussion
Defaulting date range with date slicer
Is there a way in DAX to specify the start and ending date used in a date range slicer? I need the default start date to be the first day of the month from 2 years ago and the default end date to be the last day of the previous month. So when opening the report today the start date should be 11/1/2019 and the ending date should be 10/31/2021. Both dates should automatically update to the next month as a new month starts. Is this possible?
Hi racope
You may try this Measure.
IsOrNot =
VAR Today_ =
TODAY ()
VAR Year_ =
YEAR ( Today_ )
VAR Month_ =
MONTH ( Today_ )
VAR StartDate =
DATE ( Year_ - 2, Month_, 1 )
VAR EndDate =
DATE ( Year_, Month_, 1 ) - 1
RETURN
COUNTX (
FILTER (
'calendar',
'calendar'[Date] >= StartDate
&& 'calendar'[Date] <= EndDate
),
'calendar'[Date]
)
And then, use this Measure to filter your date Slicer.
Then, the result should look like this:
For more details, please refer to the attached pbix file.
Best Regards,
Community Support Team _ Caiyun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!
3 Replies
- amitchandak
Super User
racope , no there is no option. You can log an idea for that https://ideas.powerbi.com/ideas/
There is an work around
Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA
- rbriga
Impactful Individual
I've tested this solution, but it misses out on the desired effect.
- Create a measure that will help us filter the days:
Available Dates =
VAR _LastDate =
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 1
VAR _FirstDate =
DATE ( YEAR ( TODAY () ) - 2, 1, 1 )
RETURN
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Date] >= _FirstDate
&& 'Calendar'[Date] <= _LastDate
)
)2. Select the date slicer and open the filter pane.
Under "Filters on this visual", drag our new Available Dates measure.
Select "is not blank", then apply. I suggest that you hide and lock this filter.
This limits the slicer the way you wanted, but until you move the sliders\ values from their start\end (default) positions, PowerBI assumes you haven't filtered anything, so it shows dates outside that range until you move the slider.
However, the answer may lie in the date table itself.
1. Use the query editor to add a column which flags (Yes\ No) the dates in the range you've defined. Let's call it Available Date.
2. In the filter pane, add a page\report-wide filter: "Available Date is Yes".
v-cazheng-msftCommunity Support
Hi racope
You may try this Measure.
IsOrNot =
VAR Today_ =
TODAY ()
VAR Year_ =
YEAR ( Today_ )
VAR Month_ =
MONTH ( Today_ )
VAR StartDate =
DATE ( Year_ - 2, Month_, 1 )
VAR EndDate =
DATE ( Year_, Month_, 1 ) - 1
RETURN
COUNTX (
FILTER (
'calendar',
'calendar'[Date] >= StartDate
&& 'calendar'[Date] <= EndDate
),
'calendar'[Date]
)
And then, use this Measure to filter your date Slicer.
Then, the result should look like this:
For more details, please refer to the attached pbix file.
Best Regards,
Community Support Team _ Caiyun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!
