Set Default Slicer Selection to Current Year or Month in Power BI
Вставка
- Опубліковано 21 сер 2024
- Want to set the default slicer selection to a specific period? This video shows you how. With this trick you will never have to change your default slicer to the relevant period anymore.
Find the written post here: gorilla.bi/pow...
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
SUPPORT MY CHANNEL
Any videos are made free of charge. You can support my channel by giving a donation through: paypal.me/rick....
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ua-cam.com/users/bi...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#PowerBI #DefaultSlicer #BIGorilla
![[UA] Team Vitality проти NAVI | IEM Cologne 2024](http://i.ytimg.com/vi/wttO1zVRTC0/mqdefault.jpg)
Thanks a ton for this video! You have made my day! More than a week I was struggling to solve this!
Amazing video you have no idea how hard i was searching for something as simple as this
Same!!
I did a very quick report the other day and my quick turn around solution was to replicate the page, then set a relative date for the filter then name the page accordingly.
Great that worked D Bee. The relative date can help in some instances, and when it does, please do use it. No need for more custom solutions if that worked. The video goes into details about the situations where this does not work and where you may want a more custom time period.
Cheers, Rick
Awesome stuff Rick the Programmer, Respect!
Nice video. Although wouldnt it make more sense from a report users perspective to rename 'Default' to 'Current'.
Hey Steve. That could make sense if the period is the current one. I often have the default selection on the last month that was fully finished. And then default makes more sense.
For the current month, current seems right 😁
@@BIGorilla hello, do you know of a way to make the “default” label read out the time period you want it to reflect and dynamically change the text value?
Ohhh yes!!! I've been looking for this for long time, Great video, thanks for sharing
Excellent video! It is quite intuitive actually once you put it so clearly for us hahaha. Thanks for the insight!
THIS IS BRILLIANT! SO creative! Thank you for this solution!
Awesome! Hope you can use it for your dashboards right away 🙏
This is awesome! Subscribed!
Nice trick! But hate that Microsoft didnt think of adding this basic feature. And worse I dont see anyone asking for it.. rather do these hectic workaround since Power BI was introduced. Yes, I see videos dating back to 2017 doing the same trick... No one asked for this feature?
I'm sure people indicated this before. The main question is how they should implement it. For now, hope this helps!
@@BIGorilla how should they implement?? Like how u achieved it 🤦
How come someone dislike your video? This is great stuff.
Thanks Buntri, it's all about sharing the knowledge. And those who don't want it, don't have to watch. Glad to have you on the channel :)
Thanks for the video. It works for me. Got one issue, SamePeriodLastYear function doesn't work after this. Can you please guide how to solve that please.
Nice tip! I'm subscribing! More of this please.
Thanks for letting me know. More is coming soon!
How would it be, add that the cut off day is dynamic, for example that the accounting closes on the 27th of each month or whatever day it is.
Great Vid. And I still can't believe that this is not out of the box functionality.
One question - I know my users are going to say "I don't want to see Default, I want to see the actual month", would it be OK to actually concatinate the string together - something like "Month (default)" or even "Default (month)"?
Hi Ashley,
That's a common request of users. For the slicer selection itself that would be impossible. Power BI slicer stick to the same exact value each month. That's why it should be identical.
However, you can add a separate visual in your report that shows the slicer selection.
Under my title, I often have a visual that shows:
Last refresh : xxxx | Period: mm yyyy
The period you can then show based on another month and year column in your model that contain the actual value.
I might have shown that in my written blogpost, linked to in the description.
Hope that helps!
Rick
@@BIGorilla Thanks! Yes, I explored this a bit more today and as soon as I tried the test, it became immediately apparent why.
Such a shame that this is still not standard functionality.
@@ashleyhoff7561 It would have been great if there was an out of the box option. What makes it difficult is that some want to show the current month by default, and other the latest completed month, and then again others might wanna show the next month in a slicer.
Who knows perhaps there will be a way in the future. Until then, this hopefully helps you!
Rick
Thanks for the great explanation
Big HUGE Thank You
Thanks for the video. Great tutorial
Thank you for this video. For my Dashboard I have a single Date Slider filter object that is set as in between for start and end date. How can I default the end date to always be the latest date from the refreshed data set? Appreciate your guidance, thank you
If we created a field that contained "Default -" and a dynamic month value, e.g. "Default - May" in May, would the default filter value automatically change to "Default - June" once the report is refreshed in June?
Thanks in advance!
You need to use month from default current date, then it will change dynamically
You need to use month from default current date, then it will change dynamically
At least u can increase font for people like us which use heavy lenses
For Last Problem , I guess to you can Edit Interaction between them and can make it disable then that problem will not occur.
Thanks for advised but we will need show all data then what should be do?
Thanks! I’ve used this solution for both year and month filters. If I wanted to do this same type of method but using the current business day of a month, is this possible ?
Great video. It does the job for Calendar week also, however how can I change it to ISO calendar week? any idea, please?
I'm facing an problem when I've applied the same thing to my demo work. I've a filter of fiscal year, fiscal quarter and then fiscal month.
It's easily moving to the next month if the moth falls in the same quarter, however when the quarter changes with the month changing with it, there's a problem. It doesn't deselect the first default month and selects the next default month
.
It helped me thank you!!!!!!
That's fantastic, thanks for letting me know! 😁
Ohh this is great bro! Man I need to show as a default not the current month but the last, you know? Finance results. How do I change that? Thx from Brazil. Great video!
You can change the replaced month in Power Query. Just calculate the first full month that has passed and replace that month by 'default. It should work like in the video :) cheers
@@BIGorilla it did work! You came out with a great solution! Thx again!
Thank you for this, I have a question can we use instead of default in the name, Date.Year(DateTime.LocalNow()) ? so it will show the name or number of the month ? will it work
Is it possible to select a date between 2 periods, initial and final, setting the current day as the default?
Cool! But what if it is not about dates. I have categories and product slicers. When I select a category then in the product slicer nothing is selected by default and therefore the visuals are showing nothing or mistakes. I want a default selection of product when something is getting selected in category. How can we do that?
Thank you for such videos❤. Although I have some different scenario, I have YYYYMM slicer on my report page with multiple years of data.And data is loading on monthly basis. Currently I have saved YYYYMM slicer with recent 12 months selected but client is expecting if new month data gets loaded again recent 12 months should be automatically updated. Any input on this please.
Excelent video
Nice Video which was the exact match with my requirement. But I could like to know the relationships between tables how you linked?
Hi Kamesh,
Which of the tables are you referring too?
Cool trick!
Hope it helps!
one interesting thing happening with my power bi is, I could get the today value in slicer and it is fine in my desktop but when i publish to server my selection is not retained all is showing. any idea on this? i tried single select value format option as well.
Hi, Can we do this mcode with Direct calander table? I tried and it says that have to move to import. But i don't want to do that. what can be the alternative with Direct mode?
Hi Brother, Good One, but can't we do this the same thing in relative date filtering wherein we can easily check current year, month or the past 2 years?
Yes. However, it's not slicer dependent past 2 years. And this solution is 😁
I have data up to NOV 2021 when i am WORKING in 2/Feb/2022. I have created calendar using Min( Fact_Table[Billing-Date]), MAX(Fact_Table[Fiscal-Date]). Now in my slicer i want to show latest Month NOVEMBER dynamically (by NOT selecting manually) & Previous Months. If I get any data like in Dec2021 in the back end, in the slicer i want to see Dec Month whenever i refresh the file. We are using USA fiscal policy. Use DAX ONLY
FISCALDATE FOR ALL THE DATES IN NOVEMBER IS 6/1/2021 ( COMES UNDERS FY22 ACCORDING TO US FISCAL POLICY. I WANT ALSO SHOW PREVIOUS FISCAL MONTHS IN SLICER.
bIILING DATE IS Nov/1/2021 fiscal date is Nov/1/2022
Sir, How we can do the function similar to Indirect function in Excel, in power BI. For Ex. I am having a slicer with the name of all Indian states, If I don't select any thing from slicer, then in the table for ROW value , name of state should come and their population will come from measure. If I select any state from slicer , then in the table, respective district list should appear and there population.
I think that’s native functionality of power bi if you’ve built a model…or I don’t understand your question
Grat video but could you also makethis: everytime when you clear the month filter ( or open that report) there will be actual month as default value instead of All months together?
Hi Martin, thanks for your question. I don't fully understand your requirement. What do you mean with seeing all months together?
@@BIGorilla like when i open the report there ( in month slicer ) is automatically chosen "ALL" option, which count all months together and gives data from all months to my report. And i would like to have the curent month there automatically.
Hi @@martinstrnad6860.
The video shows a way to do that. Make sure you follow the steps in the video and select 'default' in your slicer. It will hold on to this value and the default value will be the current month.
Regards
Rick
@@BIGorilla yeah that works (curent month option shows curent month + month name which i add by myself)... but I still need to chose that curent month option manually. If I dont chose it, there is ALL option and if I chose curent and close report and open again there is ALL option chosen and I want to have curent option chosen there. That mean I want to set the "Curent month option" as defalut value (now its "ALL" which is wrong for me) so when i open report or clear filter (when some exact month is chosen) there will be curent month.
@@martinstrnad6860
That not my experience when saving reports. Have you saved the report with the slicer on the 'Default' selection?
The reports I save/publish keep their selection saved. So if I save a report with June, June is persistent in the file even after I close it. The same goes for the value 'Default'.
A setting I find useful is when you are in Power BI Desktop, you can go to File -> Options -> Current File / Report Settings -> then I like to select: don't allow end users to save filters on this file in the Power BI service.
However, that is not the setting that influences this scenario. My file always keeps the slicer selection. Hope you find out what's causing yours to not do it.
Regards,
Rick
Hi Mr Gorilla, great video. What if I want to multiple months selected to the current month, how could I do that?
You would apply your logic in Power Query to replace the multiple months by the value 'Default'. It really depends on what logic you want to apply.
Why Power BI report builder propose default selection for filter but no longer for Power BI? -.-
How can I set the last 4 month to default?
Great video, thanks. Do you think this is achievable in hierarchy (year, quarter, month) format?
How to get year Default item in right place if I have future years in the year slicer list, now Default is the last one ?
You can sort the default column by the regular year column in the data view. Hope that helps!
Regards
Rick
HI Sir,
Video was good but i already have a column where i have populated dates and made the current date as today,But my requirement is report has to be opened in today's data and the user must also be able to choose the dates dynamically,is that possible, Any help would be great.
Hi Kauvery,
That's a good question. It's exactly what this video solves. You could create an additional date column (keep the original intact). In this new column, you replace the date of today by the word 'Current'. Then create a slicer that selects 'Current date' by default. When the report opens your user sees the current date.
However, they can still change the date selection in the slicer. That should solve it.
Cheers,
Rick
😂
Hi Brother, I have a question. I have a year slicer and i have 4 years data 2018,2019,2020,2021, Now if i open app in power bi service that slicer should show the year 2021. How we did this?
Hi navan,
You can either save the slicer on 2021 and save the report.
Or you can use my method from the video where you make an additional column in the calendar. In this column you replace the current year (2021) by the value 'Default'. When you then move to the next year, the default value remains the same in the slicer, but now relates to 2022.
See my written blog post for the code:
gorilla.bi/power-bi/set-default-slicer-value/#adjusted-year-column
@@BIGorilla Yeah, Thanks dood. I got the point.
Fantastic, great job!
🤦🏾♂️
But My requirement is
I have a date col in my table all the row starts with day 1 of every month and year example :
1.1.2020
1.2.2021
1.5.1995
1.6.2021
If I used date slicer with between or relative date slicer option .
I want to see only available dates in visible mode others should be hide or blur in date picker .or some other work around .
Hi nandha,
Unfortunately I don't understand your requirement. But it seems unrelated to this video. Please on-topic only 👌🙏
@@BIGorilla
This video is awesome ,I have subscribed it already ,I am regularly watching your all videos .
Based on available data in the date column ,is thr is any possibility to customize the date picker .
I don't want to show the dates without any data .
I was hoping to use this technique to show data where employee matched logged in user, but I can't seem to use USERPRINCIPALNAME outside of a measure. I can't use RLS for this as that's
a) too complicated and
b) it's per table and I need different views on different visuals, not different tables and
c) users change too frequently - maintenance would be a hassle. Does a way exist of having a slicer default to USERPRINCIPALNAME (or the full name name extracted from it)?
Hey Mr. Goriila!
What if I want to show the month name instead of Default? Like currently it's Sept, so I want it to show Sept rather than Default. Any solution for that?
Unfortunately no solution for that. Hence my workaround video! But you could use a separate column that has the regular month names, and present the month name somewhere else in the report. Something like:
Selected period : sep 2021.
That would work with this solution. Hope that helps!
you can use this: if Date.MonthName( DateTime.LocalNow()) = [MonthName] then
"Aktuální: " & Text.From([MonthName]) else Text.From([MonthName]) ...... Aktuální is Current or you can chose your own text infront of that month
How to show data in matrix visualisation for current month
Same as in the video 🙏
👍🏾 Ricks real power is patience…
what about the "preselected slicer"
I’m not sure what is your question, can you elaborate?
@@BIGorilla there is a visual called "preselected slicer" can you describe it in a video please. There is something hard to understand called "Dirty status"
I'm not sure about this visual, maybe someone else can help you out here. Best, Rick
🤌🏾