Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a date slicer that contains a rolling last 13 Month End Dates. I want the slicer to default to the latest month end date when the report is opened. I want the remaining 12 month end dates to still show in the slicer, so they can be selected if needed. For example, on April 1st, the below slicer will show dates from 03/31/2021 thru 03/31/2022 and the default selection should be 03/31/2022 with the option to change it.
Thanks.
Solved! Go to Solution.
One thing you can do is have a column in your Dates table that tags the rows you want as the default selection with "Current Month". Since it will recalculated whenever the model is refreshed, when the meaning of current month changes, the rows marked that way will also change meaning the dates for the new current month will be selected.
This is the code for my sample Dates table.
Dates =
ADDCOLUMNS(
CALENDARAUTO(),
"Month End Date", EOMONTH([Date],0),
"Month End Date Default", IF ( EOMONTH ( [Date], 0 ) = EOMONTH ( TODAY(),0),"Current Month",FORMAT(EOMONTH ( [Date],0),"m/d/yyyy"))
)
Then I set the sort by on the [Month End Date Default] to be sorted by [Month End Date]. Put [Month End Date Default] in a silcer and select 'Current Month':
Next month, when the dates roll over, the slicer will look like this but 'Current Month' will stay selected.
One thing you can do is have a column in your Dates table that tags the rows you want as the default selection with "Current Month". Since it will recalculated whenever the model is refreshed, when the meaning of current month changes, the rows marked that way will also change meaning the dates for the new current month will be selected.
This is the code for my sample Dates table.
Dates =
ADDCOLUMNS(
CALENDARAUTO(),
"Month End Date", EOMONTH([Date],0),
"Month End Date Default", IF ( EOMONTH ( [Date], 0 ) = EOMONTH ( TODAY(),0),"Current Month",FORMAT(EOMONTH ( [Date],0),"m/d/yyyy"))
)
Then I set the sort by on the [Month End Date Default] to be sorted by [Month End Date]. Put [Month End Date Default] in a silcer and select 'Current Month':
Next month, when the dates roll over, the slicer will look like this but 'Current Month' will stay selected.
Thank you. I was able to add this is.
Hey @jamoroso ,
at the current moment Power BI does not support default values for slicers.
Maybe you want to create an idea on ideas.powerbi.com or upvote an existing one.
Regards,
Tom
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |