Build a DATE PICKER in Power BI Desktop

yo what's up Patrick from Guyana cube and in this video and show you how to create a date picker in car bi stay tuned alright so a date picker what are you talking about a date picker well got a couple of emails and some comments and some videos and I was actually working on the project with someone axe this specific question hey Patrick I need to create an on screen date picker where I select just a single date not a range of dates I don't want to scroll through a list the dates I just want to choose one date and then it filters my entire report so the first thing I said was custom visual maybe use the filter panel it's too much talking you guys know what I like to do instead of all this talking let's do what let's head over to my laptop what I did was so this report let's pretend this is a report I was working on and we had this year slicer and I'm like yep there you go there's your date and they're like no we want like an individual day like today yesterday or we want to choose a day from last year but we can't use the relative dates like so don't bring that thing up I was like okay all right so I said well just just do this take the date drop it here get rid of the year and now we have our day and I see it sort it sort of descending and you have your latest date right there and now it's filtering and then I just kind of sit there like that it and they're like who you think is gonna sift through all those things I need like a little calendar to pop up and I choose a date why can't I do that in my cool you don't want to use it you don't use a custom visual so this should work and they're like no patch must oh I got another idea hang on I got another idea right so I got rid of this I said let's remove this and I opened up the filter panel and a date and I drug it on to filter for this page went to advanced and I said is I say there you go right there's a there's a date picker click apply and now you report filters now sitting there like ha ha ha yep that's what it isn't they're like ok Patrick you're not listening you're not listening I'm not I don't want to use the filter panel because I need it on screen we're gonna put some very specific filters there we're gonna lock them and we're gonna hide the filter panel we don't want people to have access to that we need this day picker to be on screen and I was like hmm so scratching my head and I said okay I gotta go take a shower that's why I do my best thinking I gotta go take a shower and then I had this brilliant idea I say oh I got it I got it all right so back on my laptop so get rid of this filter we're gonna get rid of that filter and close this guy we don't need this then I took the day and drug it over here all right I said all right this is gonna be great you guys are gonna love this and then I made it a slicer and I'm so confident in myself I made it a slicer and if you go here and click on this little carrot you'll see there's some choices between before/after yada yada yada the two that I was interested in was after him before okay so I chose after because now I have a single date and if I expand it out there's a date picker it's always excited about this I was like all right so now I have a single date and I can pick it and so I'm gonna write a measure that will filter just based on myself my selected date right so you guys may know where I'm going I was excited about this so check this out all right so I went back and I right-click here and I created a new measure it's gonna measure paint open I called it a single day total amount that was excited and I made a variable said far equals selected they selected value really really right and I was doing this and then I did this then I said return hang on one second messed that up it's all right I love it told me what was wrong return and then I say calculate write total sales amount and then I did a little treat ass right and I like to use treat ass because it assigns some lineage and it's kind of like propagating my filters are [Music] propagated my filters or by creating a virtual relationship and I should probably do a whole video on it but anyway so this little break brackets and then calendar day right and now put my value because now this will give me that it'll propagate the filter down and only return total sales for that date all right I was so excited I did that no syntax errors all right I clicked right here so test it out so I took my single day and I dropped it right there and I got blink I was like all cuz I don't have any data there right so then I'm gonna go some years hang on let's click right here let's go to 2020 and let's go to I know I have some data in May so go made 11 and it's still return blink and I was like what the French toast why is this returning plane thought about it and I thought about it and I said oh I know who know my buddy feel my buddy feels see Marc saw Carl feel up and he was like Patrick think about this for a second think about it if you using the after or before on your slicer it's not returning a single value I was like oh he'll I know where you going he was like ok yeah that's it think about it if you were to if you select after right it's gonna choose that date that's in the the picker in the the box and then all the dates after it and if you choose before it's gonna choose the date that's in the before box and all the dates that's before and so you need to change your measure if you use after you want the minimum date and if you use before you want the maximum date I was like oh thanks feel that's the tip I need it alright so let me show you what I did so I went back to my my measure and I changed this from selected value to minimum value because I chose the the after one right and now look my values changing every time I choose something that values changing based on that selected date what this is bananas now let me caveat this and I should have said this at the beginning the video you have to do this you have to implement this little pattern in every one of your measures right and so it can be a little a little tedious to create all those measures but it absolutely works if you can use the filter pane the filter pane is a little more intuitive but if you need an on screen when this approach will work so let me finish yourself let me wrap this up and show you show you what we did all right so I did that and so what I did was just to kind of make a complete solution if you look over here in the in the fields list I have a folder where I put all of my single day this is what I call my single day measures and you can see the exact same pattern across each one it's the exact same measure the exact same pattern where I'm using that minimum date because I'm using the minimum I'm using the after slicer and then over on my daily report all I do now is I use those measures throughout you can see single day you can see single day they're used throughout that report and then I grab my date I drop it on here right drop my date right there and what I'm gonna do is I'll still have that filter there yep let me get rid of that filter I don't want that filter there all right and so what I'm gonna do is make this a slicer I love to do little things like this in video I'm gonna make this my after and then I'm gonna format this up just a little bit this is actually what I did for when I delivered this and then I'm gonna go here to my format option I'm gonna turn the slider off right turn the slider off and then I'm gonna go to the data inputs I'm gonna increase this to like 17 right and then I'm gonna grab a and then I'm gonna change the title to turn the title on yeah there we go and say select a date and change the color to black increase the font to about sixteen right and then turn the header off and do one last thing right one last thing I'm going to insert a little bitty rectangle right there watch this little trick that I do right there on it and then say no feel like yes I do want to feel sorry about that I'm gonna change that to white and change the line color to white and now right it looks like a little simple little date they picker and then I'll go down to 2020 and choose any day and you'll see as I select my dates that the values actually change what this bananas it works is so easy I know there's some steps that you gotta take to get this working but if you want that on screen date picker you don't want to use the filter panel you don't want to use the a custom visual this absolutely work requires a little work but it definitely solved my problem it was the solution I needed for the problem that I was faced with all right what do you guys think getting questions getting comments let's continue the conversation we're in the comments below it's your first time visiting the guy in the cube channel hit that subscribe button you like my video big thumbs up as always from Adam myself thanks for watching we'll see in the next video

Channel: Guy in a Cube

Views: 94,439

Keywords: power bi desktop, power bi desktop tutorial, power bi, power bi dashboard, power bi date picker, power bi date picker slicer, power bi dax, power bi demo, power bi for beginners, power bi reports, power bi training, power bi tutorial, power bi tutorial for beginners, single date picker in power bi

Id: zhWtU0DynCk

Length: 10min 14sec (614 seconds)

Published: Wed May 13 2020