Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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!
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!
I've tested this solution, but it misses out on the desired effect.
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".
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |