Forum Discussion
Helper IIIPrevious month selected automatically from drop-down slicer
Is there a way to have a drop-down slicer select a previous date by default, but ALSO still have the other options available to choose from? Like the image example below, I would like 'Mar-26' to be selected for users who are viewing the visuals on first load.
I tried the Relative Date filter but it only allows the the singular month(s) listed with no other option to select other previous, present, or future months.
However I was hoping the slicer display would still show the 'Mar-26' date in the slicer instead of 'previous Month.' Unfortunately, this is not possible. The key of this workaround is being alphabetically different and always be on top of the slicer. Maybe you can add
var _monthName = FORMAT(_previosMonthEnd, "MMMM")
and
"Previous Month (" & _monthName & ")".
If this solved your issue, please mark it as the accepted solution. ā
9 Replies
sevenhillsSuper User
If you are ok with the custom visual, check this one out. Little buggy but ok it works
Preselected Slicer
Note: This is NOT my app visual. It has a sample file to play with it.- anilelmastasi
I don't understand why product team develop a slicer like this, instead we are doing workaround. I know this visual but I haven't use in my reports. My biggest client (3000+ user) used this developer's Mass Filter in prod and it works ok.
- sevenhills
Usage wise, it is simple and helps folks like analsysts. With few steps easy to achieve the functionality.
In my view, I did not dig, but thinking as short cut to Reusable Time Intelligence Calculation Groups with TMDL.
- Shai_Karmani
You don't need the relative date filter for this. Use a regular drop-down slicer on your YearMonth (or month) column, click into it in Power BI Desktop, pick Mar-26 yourself, then save and publish. Whatever was selected when the report was published becomes the on-load selection for every viewer, and the dropdown still shows all the other months so users can switch freely.
One thing to watch: in Power BI Service, persistent filters remember each user's last selection, so a returning user may see their previous month rather than your default. Anyone in that situation can click Reset to default at the top of the report, or you can disable persistent filters in the report settings if you want the default applied on every open.
If this helps, please give a thumbs up and mark it as the accepted solution.
Best,
Shai Karmani- chillpill
I understood this to be a solution but it requires manual input to update every month. Is there a systematic way to achieve this?
- anilelmastasi
This is a very manual way to do this.
- anilelmastasi
Hello chillpill ,
Yes, there is a workaround for this. Let me explain how.
In your date table you should create a new calculated column. If it is from sql you should add a column or you can add it in your power query too.
Step 1- Create a new column in date table.
You should create a new calculated column. I made it dynamic and it calculates on today's date. You can change. As an example:
PM Column = VAR _today = TODAY() VAR _previosMonthStart = EOMONTH(_today, -2) + 1 VAR _previosMonthEnd = EOMONTH(_today, -1) RETURN IF( 'Dim Date'[Date] >= _previosMonthStart && 'Dim Date'[Date] <= _previosMonthEnd, "Previous Month", FORMAT('Dim Date'[Date], "yyyy-mm") )Step 2- Change the column in the slicer with the new calculated column. And sort by descending. With sorting descending, previous month stays always at the top.
Step 3- Publish to service with previous month selected. In calculated column, previous month updates dynamically and it opens always with previous month selected.
If this solved your issue, please mark it as the accepted solution. ā
- chillpill
This is a great solution and what I've seen elsewhere, thank you! However I was hoping the slicer display would still show the 'Mar-26' date in the slicer instead of 'previous Month.' Although now that I think about the sort order of the slicer being based on that calculated column, maybe I can dynamically update that column to order by 1, 2, 3, etc based on the month and do it that way. Thanks for the idea!
- anilelmastasi
However I was hoping the slicer display would still show the 'Mar-26' date in the slicer instead of 'previous Month.' Unfortunately, this is not possible. The key of this workaround is being alphabetically different and always be on top of the slicer. Maybe you can add
var _monthName = FORMAT(_previosMonthEnd, "MMMM")
and
"Previous Month (" & _monthName & ")".
If this solved your issue, please mark it as the accepted solution. ā
