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

Подписаться 22 тыс.

Просмотров 99 тыс.

50% 1

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/power-bi/set-defau...
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/rickmaurinus.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
ru-vid.com?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#PowerBI #DefaultSlicer #BIGorilla

Опубликовано:

27 апр 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 94

@balajisv1118 7 месяцев назад

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

@elbezz Год назад

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

@MuriloLimaRibeiro 2 года назад

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

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

@rreneijhorst4418 11 месяцев назад

Awesome stuff Rick the Programmer, Respect!

@ajaaskelainen 2 года назад

This is awesome! Subscribed!

@mbielby1 Год назад

Thanks for the great explanation

@NinjaGames443 2 года назад

Thanks for the video. Great tutorial

@thousandsunny100 3 года назад

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

@BIGorilla 3 года назад

Thanks for letting me know. More is coming soon!

@Wheazul Год назад

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

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

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

@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

@ToolTimeTabor 5 месяцев назад

Big HUGE Thank You

@martinkardos3868 Год назад

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

@balajisv1118 7 месяцев назад

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.

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

@saradachellam3185 3 месяца назад

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

@avinashnl9588 2 года назад

It helped me thank you!!!!!!

@BIGorilla 2 года назад

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

@GajanandTiwari-kh5jp 6 месяцев назад

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

@RogerSlides Год назад

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

@seazonegranec 9 месяцев назад

Excelent video

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

@willyms 3 года назад

Cool trick!

@BIGorilla 3 года назад

Hope it helps!

@user-mq2vz5yh6g Месяц назад

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

@jay6817 2 года назад

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 2 года назад

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

@smilekp1 7 месяцев назад

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?

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

@akshayshelar6167 8 месяцев назад

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 .

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

@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 9 месяцев назад

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

@santhoshsanmo 9 месяцев назад

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

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

@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 Год назад

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

@brunoalvescaetano 2 года назад

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 2 года назад

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 2 года назад

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

@shivangnigam7287 Год назад

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

@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 2 года назад

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 🤦

@HuyNg2603 2 года назад

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

@BIGorilla 2 года назад

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.

@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

@numanpiloglu8580 Месяц назад

How can I set the last 4 month to default?

@nirajvora9604 2 года назад

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 2 года назад

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

@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

@RavinderKumar-gl8cy 11 месяцев назад

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 11 месяцев назад

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

@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 11 месяцев назад

😂

@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

@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

@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 11 месяцев назад

🤦🏾‍♂️

@mayanknanda4869 2 года назад

How to show data in matrix visualisation for current month

@BIGorilla 2 года назад

Same as in the video 🙏

@cathrerinezetadrones3169 11 месяцев назад

👍🏾 Ricks real power is patience…

@tutruongquangminh3 Год назад

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

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

@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

@cathrerinezetadrones3169 11 месяцев назад

🤌🏾