Set Default Slicer Selection to Current Year or Month in Power BI

No video

Set Default Slicer Selection to Current Year or Month in Power BI

Рет қаралды 103,835

Күн бұрын

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
kzfaq.info...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#PowerBI #DefaultSlicer #BIGorilla

Пікірлер: 98

@balajisv1118 9 ай бұрын

Thanks a ton for this video! You have made my day! More than a week I was struggling to solve this!

@Wheazul Жыл бұрын

Amazing video you have no idea how hard i was searching for something as simple as this

@FamilyNightBytes 15 күн бұрын

Same!!

@balajisv1118 9 ай бұрын

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.

@rreneijhorst4418 Жыл бұрын

Awesome stuff Rick the Programmer, Respect!

@eladiobardelli3001 Ай бұрын

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.

@elbezz 2 жыл бұрын

Ohhh yes!!! I've been looking for this for long time, Great video, thanks for sharing

@CAKimberlyLewis 2 жыл бұрын

THIS IS BRILLIANT! SO creative! Thank you for this solution!

@BIGorilla 2 жыл бұрын

Awesome! Hope you can use it for your dashboards right away 🙏

@jay6817 3 жыл бұрын

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.

@BIGorilla 3 жыл бұрын

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

@MuriloLimaRibeiro 2 жыл бұрын

Excellent video! It is quite intuitive actually once you put it so clearly for us hahaha. Thanks for the insight!

@BuntriGuyTech 3 жыл бұрын

How come someone dislike your video? This is great stuff.

@BIGorilla 3 жыл бұрын

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 :)

@stevewilliams9753 3 жыл бұрын

Nice video. Although wouldnt it make more sense from a report users perspective to rename 'Default' to 'Current'.

@BIGorilla 3 жыл бұрын

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 😁

@nickthies3099 2 жыл бұрын

@@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?

@ToolTimeTabor 7 ай бұрын

Big HUGE Thank You

@thousandsunny100 3 жыл бұрын

Nice tip! I'm subscribing! More of this please.

@BIGorilla 3 жыл бұрын

Thanks for letting me know. More is coming soon!

@mbielby1 Жыл бұрын

Thanks for the great explanation

@saradachellam3185 5 ай бұрын

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

@ajaaskelainen 2 жыл бұрын

This is awesome! Subscribed!

@GajanandTiwari-kh5jp 8 ай бұрын

Thanks for advised but we will need show all data then what should be do?

@user-mq2vz5yh6g 3 ай бұрын

Is it possible to select a date between 2 periods, initial and final, setting the current day as the default?

@namangarg7023 22 күн бұрын

At least u can increase font for people like us which use heavy lenses

@NinjaGames443 2 жыл бұрын

Thanks for the video. Great tutorial

@SamuelRoshan 3 жыл бұрын

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?

@BIGorilla 3 жыл бұрын

I'm sure people indicated this before. The main question is how they should implement it. For now, hope this helps!

@mshanabar Жыл бұрын

@@BIGorilla how should they implement?? Like how u achieved it 🤦

@macshock632 Ай бұрын

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?

@martinkardos3868 Жыл бұрын

Great video. It does the job for Calendar week also, however how can I change it to ISO calendar week? any idea, please?

@WillSM0001 2 жыл бұрын

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 ?

@akshayshelar6167 10 ай бұрын

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 .

@ashleyhoff7561 2 жыл бұрын

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)"?

@BIGorilla 2 жыл бұрын

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

@ashleyhoff7561 2 жыл бұрын

@@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.

@BIGorilla 2 жыл бұрын

@@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

@shivangnigam7287 2 жыл бұрын

For Last Problem , I guess to you can Edit Interaction between them and can make it disable then that problem will not occur.

@taliziv5939 Жыл бұрын

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

@smilekp1 8 ай бұрын

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?

@avinashnl9588 2 жыл бұрын

It helped me thank you!!!!!!

@BIGorilla 2 жыл бұрын

That's fantastic, thanks for letting me know! 😁

@brunoalvescaetano 3 жыл бұрын

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!

@BIGorilla 3 жыл бұрын

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

@brunoalvescaetano 3 жыл бұрын

@@BIGorilla it did work! You came out with a great solution! Thx again!

@numanpiloglu8580 3 ай бұрын

How can I set the last 4 month to default?

@Deepak-iq5ul Жыл бұрын

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.

@sivraj2k9 Жыл бұрын

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.

@alldayvideosallday 2 жыл бұрын

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!

@santhoshsanmo 11 ай бұрын

You need to use month from default current date, then it will change dynamically

@santhoshsanmo 11 ай бұрын

You need to use month from default current date, then it will change dynamically

@seazonegranec 11 ай бұрын

Excelent video

@tutruongquangminh3 Жыл бұрын

Why Power BI report builder propose default selection for filter but no longer for Power BI? -.-

@hmm3691 3 жыл бұрын

Nice Video which was the exact match with my requirement. But I could like to know the relationships between tables how you linked?

@BIGorilla 3 жыл бұрын

Hi Kamesh, Which of the tables are you referring too?

@RavinderKumar-gl8cy Жыл бұрын

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.

@cathrerinezetadrones3169 Жыл бұрын

I think that’s native functionality of power bi if you’ve built a model…or I don’t understand your question

@nishantchawla333 3 жыл бұрын

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?

@BIGorilla 3 жыл бұрын

Yes. However, it's not slicer dependent past 2 years. And this solution is 😁

@martinstrnad6860 2 жыл бұрын

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?

@BIGorilla 2 жыл бұрын

Hi Martin, thanks for your question. I don't fully understand your requirement. What do you mean with seeing all months together?

@martinstrnad6860 2 жыл бұрын

@@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.

@BIGorilla 2 жыл бұрын

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

@martinstrnad6860 2 жыл бұрын

@@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.

@BIGorilla 2 жыл бұрын

@@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

@willyms 3 жыл бұрын

Cool trick!

@BIGorilla 3 жыл бұрын

Hope it helps!

@Sandroider 2 жыл бұрын

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 ?

@BIGorilla 2 жыл бұрын

You can sort the default column by the regular year column in the data view. Hope that helps! Regards Rick

@RogerSlides 2 жыл бұрын

Great video, thanks. Do you think this is achievable in hierarchy (year, quarter, month) format?

@kummithavenkatareddy2302 2 жыл бұрын

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

@HuyNg2603 3 жыл бұрын

Hi Mr Gorilla, great video. What if I want to multiple months selected to the current month, how could I do that?

@BIGorilla 3 жыл бұрын

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.

@nandhakumar6480 2 жыл бұрын

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 .

@BIGorilla 2 жыл бұрын

Hi nandha, Unfortunately I don't understand your requirement. But it seems unrelated to this video. Please on-topic only 👌🙏

@nandhakumar6480 2 жыл бұрын

@@BIGorilla This video is awesome ,I have subscribed it already ,I am regularly watching your all videos .

@nandhakumar6480 2 жыл бұрын

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 .

@kauverysaravanan9035 2 жыл бұрын

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.

@BIGorilla 2 жыл бұрын

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

@cathrerinezetadrones3169 Жыл бұрын

😂

@navaneetharajuramesh8462 2 жыл бұрын

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?

@BIGorilla 2 жыл бұрын

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

@navaneetharajuramesh8462 2 жыл бұрын

@@BIGorilla Yeah, Thanks dood. I got the point.

@BIGorilla 2 жыл бұрын

Fantastic, great job!

@cathrerinezetadrones3169 Жыл бұрын

🤦🏾‍♂️

@mayanknanda4869 2 жыл бұрын

How to show data in matrix visualisation for current month

@BIGorilla 2 жыл бұрын

Same as in the video 🙏

@cathrerinezetadrones3169 Жыл бұрын

👍🏾 Ricks real power is patience…

@GaryMillerUK Жыл бұрын

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)?

@nirajvora9604 3 жыл бұрын

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?

@BIGorilla 3 жыл бұрын

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!

@martinstrnad6860 2 жыл бұрын

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

@kadirkkkk 2 жыл бұрын

what about the "preselected slicer"

@BIGorilla 2 жыл бұрын

I’m not sure what is your question, can you elaborate?

@kadirkkkk 2 жыл бұрын

@@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"

@BIGorilla 2 жыл бұрын

I'm not sure about this visual, maybe someone else can help you out here. Best, Rick