How to set a DATE SLICER to TODAY by default in Power BI

can we set the default value for our date slicer to today in this video i'm going to show you how if you're new to this channel and you want to improve your power bi skills then make sure to hit that subscribe button and let's get started [Applause] [Music] so what i want to achieve is to create a slicer that always opens up on the date of today when i open up the report now let me show you what i mean on a simple table now here on our report page we have a list of invoices with the invoice amount now i would like to add a slicer to it and on the slicer we're going to have our date field from a custom day table okay so let's drag it onto our slicer and i'm gonna change it into a list so that we have a list of all of the dates now let's sort this then in descending order now that we have a slicer one way to filter on today's date is to go down the list and look for today's date now today we have the 10th of september now there it is then select it and you see now we only have the invoices for today however it would be nice if this is the default when i open up my report okay now you might think well maybe we can just put a filter on a slicer for today okay now let's see if this is possible so i'm going to select the slicer visual then go to filter section and here i would like to have a filter on date and i'm going to change basic filtering into relative date filtering and here i can say it's in this day and then apply the filter and let's see what happens to our slicer well it shows today's date um however i cannot switch to the other dates anymore and this is not exactly what i was looking for so let's look for a workaround okay so i'm gonna undo this now to build and work around to have the slicer always filter on the today's value when you open the report we have to go to our date table now here i build a custom date table using the calendar auto function and i just added the year quarters the months and the days okay and what we're going to do now is add another column to our table okay so i can go here and add a new line and this one is going to be my slicer date now let's put the next part on a new line now here we're going to check if the date equals today now this we can do with a function okay so we want to check if the date equals today so for this we have today function and if so then we are going to return the text today now what if it's not well then we want to check for another condition so i'm going to have a nested if now we want to check if the date is lower than today and if it is then we want to return the date okay so let's close the first if function and then let's also close the other one let's see if this works and you see it gives me an error let's see what's in going on so the dex expression for the calculated table then date results in a variant data type for column slicer date now what's going on is the following here we want to return text today and then we want to return date so date data type and text data type we have to choose we have to be consistent so what we can do is just do the following i go back here to date and then do ampersand and then quotation mark quotation mark so i add text to it so 10 things okay that's going to be text so then we have text here text there and you see now we resolve that error now why are we doing this because now we have one column slice a date that returns nothing when we have a date that's after today but if i scroll down you see for today's date it returns the tax today and then for all of the dates before that it returns the date so what can we do now well we can use this slicer date for a slicer okay so let's go back to a report here we have a slice i i'm going to take away the date field from it and i'm going to replace it with our slicer date so let's drag it onto the field now over here there's still the filter from before so let's get rid of that filter and here you see we have a blank and we don't want to show blank okay so let's then go back to the filter section and let's now say over here that we want to have an advanced filter and it should not contain blanks let's apply it all right so now we want to reverse the orders that we have in descending order and you see that today is now at the top however the sorting of all of the other dates that doesn't make sense yet okay so we can select today however then if today is the 10th of september then i want to show right below it the 9th of september okay so still not perfect yet now what we have to fix is how it sorts the slicer date column okay so and this we can do with sort by okay so i'm going to go back to our date table and i'm going to select the slicer date column click here and sort by column and i want to sort it by the date and again we have an error so what's going on here so sort by another column we cannot sort the slice a date column by date there cannot be more than one value and date for the same value and slicer date please choose a different column for sorting or update the date and date so what the problem is is that we have a lot of blanks in the slicer date column okay and we have multiple values for those blanks in the date column so it doesn't let us do this so how can we work around this what we can do is the following and go back to the formula that creates my custom date table so over here we have calendar auto and for the moment i'm just going to replace this with a calendar function so we have data that starts at the beginning of 2019 so i'm going to use a date function so 2019 month one day one let's close the date function then we're going to have another date function for the ending so that's going to be year 2020 month today it's september and then day done because today we have the 10th of september i have to make sure here that i don't exceed at the today's date and then we don't have any blanks now then i go back to sort by and sort by the date so you see it lets us change the sort by column because we do not have any blanks anymore however it is important that your date table goes until the end of the year so i have to change it back okay so i go here back to my formula and i'm going to put in calendar autumn again just like it was before and we have our blanks again so did we lose the sort by column let's have a look well you see it still is sorting by date so why this works it's a little bit mysterious however it gets the job done so let's have a look at a report and you see that we have now the today value at the top and then in descending order all of the other dates okay so now when we save a report with today selected and we open it up tomorrow then we will have the 10th of september showing its date and of course the 11th of september will then be today if you know other ways of how to achieve this then share it with us in the comment section below and if you want to improve your power bi skills then don't forget to hit that subscribe button i hope to see you in the next video

Channel: How to Power BI

Views: 152,537

Keywords: power bi date picker default to today, power bi date slicer default to today, slicer default today, date slicer default value, power bi date slicer, slicer default option, date slicer not working power bi, date slicer power bi default value, how to set a slicer to today, date slicer, slicers in power bi, set date slicer default, date slicer default in power bi, power bi date sliver default value, power bi, power bi tutorials for beginners, power bi desktop

Id: VaJ10ilXp6Y

Length: 9min 1sec (541 seconds)

Published: Fri Sep 11 2020