Build a DATE PICKER in Power BI Desktop
Вставка
- Опубліковано 21 сер 2024
- Have you ever wanted a single date picker in your Power BI report? Patrick shows you how to build one within Power BI Desktop!
Download sample: guyinacu.be/da...
📢 Become a member: guyinacu.be/me...
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/co...
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com...
#PowerBI #GuyInACube
![[UA] Team Vitality проти NAVI | IEM Cologne 2024](http://i.ytimg.com/vi/wttO1zVRTC0/mqdefault.jpg)
Hi Patrick, thank you for the trick but it will make much more sense if Power BI team makes a date picker by default since this is a really basic reporting need.
Totally agree!
agree, especially that white box and slicer itself is another performance utilizator, great tip though !
All this effort for a single date selection.
Agree on this
It’s insane how tableau is light years ahead as a product with functionality. Power BI will eventually catch up. You can’t even hide a column in a table viz!
I had a similar requirement recently. What I did is created a calculation group named Single Date Activator, with logic: CALCULATE(SELECTEDMEASURE(), FIRSTDATE(Calendar[Date])). So whenever users want measure to be applied on single date they put the calculation group as a filter in report and all measures will be switched to single date.
I like that he went through the other options at the beginning that I had already tried. That way I knew I would get an answer.
Good solution. Worked through this with a client recently and to avoid changing all the measures we used the new hierarchy support on slicers and did a simple year / month / date hierarchy. Only caveat here is that even with single select enabled you can "single select" at any level of the hierarchy, so users may end up selecting a whole month instead of a single day. Given the 100s of measures used, the compromise was worth it
love it! Always a give and take. As you call out - best to understand the ramifications and then make a decision on best course.
It would be even more bananas if Microsoft added a functionality like that out of the box. 😉 but thanks for the video, I messed around with that a lot.
Would be great if it was there out of the box.
@@GuyInACube BTW: you guys rock!
Hello Patrick, I think a quick way to hide the second date picker box, is to turn off the responsive option and then set the size of the slicer in a way that the second box stay off the frame.
ohhhhh nice. Will have to give that a try. Thanks for that! 👊
It works but then date slicer looks very tiny.
Why in the world is there *STILL* no single date mode for the Filter Visual?!?! It boggles the mind. While I love DAX (only since I went through the Mastering DAX class) and I certainly appreciate this patchwork type solution (until you need to enable the border which would highlight the presence of whiteout patch, which would require a patchwork border as well to compensate), it makes PowerBI seem so incomplete compared to other BI offering on such a rudimentary requirement. This is the kind of detail that I continually had to work around for my last customer. Really painful.
Martin Turcotte I know, right? You have to fudge EVERYTHING to get such simple functionality. It makes maintenance a nightmare.
I totally agree with you... Maybe it would be great to all vote here : ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20245006-select-a-single-date-from-pop-up-calendar-in-date
ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20544478-single-date-picker-in-power-bi-desktop
Couldn't agree more :-)
Which DAX class is that ? Thanks
Had this exact request in the last couple of days, ideal. Thanks Patrick, super helpful as always!
Awesome! Glad it was helpful 👊
Thanks for the suggestion, I have implemented this and got it working as I have dozens of measures I needed duplicates of every measure which was very confusing both to me and any future user. I have resorted to ripping this back out, deleting dozens of excess measures and relying on users to select a single date. Please, please Microsoft add this as a standard option. It is a basic reporting requirement and this work around is too cumbersome for large dashboards.
To pick a date, it takes 5 clicks using the calendar, but if you have 3 separate slicers, year, month and day, it takes 3 clicks to pick a date and doesn't require any coding/new measures, but it will take up more space.
Hey Patrick, thanks for the Video, many people have been requesting this feature for a long time. I had to write my own Custom Visual, because my users wanted the Calendar (datepicker) to show Yesterday's value as a Default when they open the Report, and did not want to see dates in a drop-down list for a cleaner look. So I used a web tool from SyncFusion which provides javascript control code to allow you to change fonts, and colors of the calendar and also synchronize it with all pages in the other Report Tabs. It works very well, but I had to learn NodeJS, Javascript, Ms Code, HTML and Power BI scripting language to make it all work. I can share it with you if you want for others to enjoy it (or suffer) :)
Hi Andreas, it would be great if you could share your visual in any way - GitHub, PM, whatever you like :-)
Could you please share it, I need that for my reports.
Hey Patric, Thanks a lot for the video. I would like to share a cool tip over here, you can use the same calendar trick but instead of putting min date in the measures, you put them as a filter in the graph. This way all of your measures work without having to change them.
Yes, that's so much easier.
Hey Patrick, great video as always. In some cases you may be able to avoid the need for tailored measures by applying the TopN/BottomN filter to the visuals. If using the 'After' date slicer, add a filter to your table, chart etc visuals for Date, then Advanced Filtering - TopN and enter Bottom '1' to get 'Earliest Date'. This should have the effect of reducing your range of dates to a single date. I use this technique also where some visuals need a date range and some need the end date of the range.
I used the same technicue but when I aplied filter to visual with slicer if there is no data for that slicer value instead of showing blank it showing data
great content as always - however why is this not a standard feature already?!
he he awesome . This is absolutely a workaround. Suits well for small projects. Project with 100's of Measures will be difficult to maintain.
I use a visual called Beyond Soft Calendar. You can add info to the dates, and also you can click any date to filter the report. Very nice!
Patrick explains it like im a dummy. and to be honest. i love it !
Hey Patrick nice video. I think the rectangle shape should be moved to front for foolproof functionality. What do you say?
You will need to else it'll keep going back to the background making the slicer visible. This is exactly what I did in one of the reports I created for my company last year!
Hey Patrick! I was wondering if you had a idea / solution for creating custom periods using the date picker functionality? In short what I'm trying to create is a date selector that allows the end user to define a custom start date for a "Contract Year" rather than seeing values by company fiscal year or calendar year. If a contract was to start on March 20th 2021, the end user could select that start date from the date picker which would define contract year periods for the remainder of the dataset: {CY1 = 03/20/21 - 03/19/22} {CY2 = 03/20/22 - 03/19/23} Etc.
Thanks for the read and all the work you put into teaching Power BI! It has been immensely helpful!
Hi Patrick,
nice solution. I would like to add though that for your example I think there's also an alternative to adjusting all your measures. If you would create a single extra measure like:
Date_diff = CONVERT(CALCULATE(MIN('Calendar'[date]))-CALCULATE(MIN('Sales'[date]));INTEGER)
and then use this in the filter pane as a visual filter where Date_diff = 0, all measures in the visual that are based upon the 'Sales' table will be filtered at once. I know there are two challenges here: [1] page filter doesn't work with measures, so you need to check all visuals [2] if there are multiple source tables (like 'Internet Sales' + 'Store sales' + ...) then this is required for all tables. As a result it will depend on the scenario if it's useful, but I think in the example it might be the more lazy way.
PS1: boolean won't work when using a measure to filter, therefore I subtract and convert to integer
PS2: issue [2] can be sorted out by creating a second date table Date_diff = CONVERT(CALCULATE(MIN('FilterCalendar'[date]))-CALCULATE(MIN('Calendar'[date]));INTEGER) and use FilterCalendar in the slicer
Patrick - You are super helpful and this is ALMOST what I need but I need to pick a start AND an end date and do the calculations on or after the start date/time and then on or before the end date/time. The time component on the date is critical. I'm literally pulling my hair out as I'm also new to Power BI, rusty at programming and trying to solve a big issue in my new job! To explain a bit more I need to track power outages and restorations during a major storm event. Each outage has a start date/time and an end date and time. There can be multiple outages during a major storm event and obviously, they don't all begin/end at the the same time...an outage that starts on day one can last through day 3 while there are multiple other outages opened and closed in the same time period but I need to account for them all.
I wish Power Bi just had a date picker already! And a default calendar table! Come on, Power BI!!
Shouldn't this be called Guys in a Cube? I greatly appreciate both of your work.
I have not had a need yet for this but can see how I can implement this in future projects. Glad I saw the video because I assumed there was a calendar icon I could use that would do single dates. Now I know. Thanks.
I am at a loss for words, It is marvelous. Appreciated
Nice video. Love the way the Guy in a Cube videos are always entertaining - can see that you guys are passionate about BI reporting, as am I. I've been doing BI for a while but you opened my eyes with the possibilities in this video.
I watched this and then immediately got asked to do pretty much this by my boss
Yeah for the clients that are picky like that lol. Cool video, but I had to do it differently. DAX calendar with 3 years of dates prev, pres, future. Plus additional logic to display "Today" , "Last Quarter" , "Year-To-Date", "Previous Year" etc in a slicer 🤷🏽♂️
Thank you very much for this, i have a same question and i tried everything to resolve but didn't get correct answer but now use "Treat" Measure i get answer. thank again.
Great trick! needed it some time ago. But I am sure I will need it again!
Hi Patrick,
Thanks for the video.
How to use a "drop down" date slicer to choose Before date(Not before date slicer).
I searched this community, asked a question but couldn't find what I need.
I want to create a Report Date Slicer. For example when you choose November 23, it should show November 23 and before. The reason is, I have visuals that shows values against date.
Oh dam! So simple but so good. This is perfect. Your a dude!!!! ✊🏽
Has helped us out for sure! 👊
Thank you so much!! I had this problem weeks ago.
Glad this is useful for you! 👊
Man, so funny, so powerful! Subscribed. Thank you!
Thanks for watching! 👊
It's more simple to create a table calendar with all types of dates format and by doing a relantionship between the table you use and the calendar to match the results of the visual you want to create and interact with the date slicer.
Hi Patrick, Thanks for the amazing video. Just wanted to check how to implement two date pickers, like start date and end date in one viz.
I loved the solution. Thanks!
Love you Patrick from Pratik for this Trick!
I was looking at the video and I thought, Why on earth is patrick using selected value instead of max/min? After waiting more for the video to end, I understood your great pedagogic idea. Thanks!!
Hi Patrick, awesome video as usuall !! Love that kind of tips. You guys from GIAC help a lot. Thanks for that
Nice video, love your enthusiasm, Patrick!
Noz thought this through fully, but wouldn't it be easier to apply a flag to a whole set of already finished measures, if you want to do this?
Something along the lines of:
-set up an independent date table
-apply this 'after' slicer to that
-now we can get the min value in a measure
-but we can also compare the independent date with the one that the report already relies on and output true or false
-now we only filter the entire report page by 'flag=true' and all measures should filter correctly
Just an idea, if you otherwise need to switch dozens of measures within a big report.
Great Patrick Gonna use this one, a bit differently. I am gonna use this as a time travel
We did the same trick for a report that has to be opened with the last available date by default, but should be switched to any other date :) We’ve used the Before option though, with MAX in the Dax formula :)
But still this whole process would be redundant if there was the same calendar popup with a single date selection in Power BI, not with the before/after options.
Hi , just got into this . Incredible 2 years later and still no decent solution . Anyways , I wanted to ask how did you do it to use a date picker and use the latest available date , as of now even if I refresh, I get the last I choose in the picker
Thank you a lot! Your video helped me to find a decision of my problem
It i incredible that such basic visualisation options are still not default for Power Bi. I swear, this program tends to mess up in most basic of things.
"This is bananas"... Great Thaks Patrik.
I made a data picker but I used the 'date number' and 'Year Month' columns from my calendar table (in 2 different slicers) and turned single select on both. This way I don't have to modify the measures.
Another way we can do this is put the date in a hierarchy slicer.
What do you think?
P.S. - It won't show the nice little calendar but surely reduced the effort of going through every single date.
Hi Patrick! First of all, thanks a lot for the video! As you like to take things to the next level, would it be possible to default the selection date of your date picker to show "Today's" date? Otherwise, whatever final date selection is made, whenever we close and open the report again that same selection is going to be displayed. I hope my explanation makes sense. Thanks!
It’s really helpful. Thanks for your sharing. Best wish for you
Thanks a lot!
Hi Patrick Thanks a lot for this... My question is what if I wanted to retain the date picker but because its a live sales data i want it to default to today.
This is great! Just one simple question...why is a date picker not built in to PBI?
Thanks for the tip. Anyway, it feels overly complicated and also that Microsoft forgot(?) to add single date picker by default. I bet t will come in one of updates, hopefully already this year. Have to check ideas list...
Hahahhaha you´re right about the forgetting part
quite insane that there isn't an 'equal to' option in the date slicer in the first place!
Helpful used the same in my project. But Power BI team should add this date picker visual in visuals as standard option
Bro that was awesome. Thank you
Great Stuff !
Hi Patrick,
Thanks for the logic. However I have an issue with this. My requirement is I need to choose the single date picker value and based on the selected date I need to do a conditional check for a new column and based on the conditional check result populate the column. Like for example if I pick a date from the date picker I should compare my value date column against this chosen date and decide the status of the record as active and inactive. Is it possible?
I have a similar requirement, did you find a solution to this?
Amazing !
My Man!!!!!!!!!!!!!!!!!!!!!!!!
Nice clear video
Appreciate that Ronald! Thanks for watching 👊
Hi Patrick, loved the way you explain. However, I have a challenge. In the above example - I am pretty sure you must have connect the dates through data model. What in case, in some situation where my date is disconnected to data model.
Example below:
I have two date columns in my table (Start Date and End Date).
I want to give a calendar picker to my user in visualization page. Based on the date selected by my user, I want to calculate two columns:
First column: "How many days completed?"
Formula: Selected Date - Start Date + 1
Second column: "How many days to GO" ?
Formula: End Date - Selected Date + 1
Is it possible ? Initially, I thought to keep dates as parameter. But we don't have DATE datatype. Please help.
Please make up a video on treatas, thanks!
Guy in the cube, I have a challenge for you and it revolves all around hierarchies
How do we do something similar but for Time? I need a clock to select Hours and Minutes. Couldn't find a decent Clock custom visual until now
Hello Patrick, thank you for this video, it was very interesting solution for a single date picker. Is there any way to limit the range of dates using the default date picker with between option? year list increase or decrease 10 years for a year selected. I want to limit it between min date and the current date.
Hi patrick. i'd be interested to see the implementation of Calculation Groups for similar Measures
That would be interesting to look at. Thanks for the suggestion 👊
@Great tip for Date Picker, is there any chance to set default slicer value to Today always and date picker?
Hi Patrick. Brilliant video but if instead of a card, you need it in a bar chart how would you about it? Really struggling with that
Thanks for making BI funzy
Hi Patrick, thats is absolutly awsome :D it's a shame that PBI dosen't have a buildt-in option for a calendar picker
Would you be able to do a video including the same thing but with a hour picker, similar to the filter pane ?
This was masterpiece
Hi Patrick, I just wanna say ur awesome
So this variable will bring you the total amount of the day selected or the day right after, in your example you select may 11th, the total amount will be for the 11th or 12th?
thanks! cool workaround.
Awesome stuff !
This is a really cool trick! I’m wondering if you could create a measure to flag it’s that date and apply it to each visual instead of recreating the measures too. I’ll have to try it out later!
Hi, Patrick. Thank you for this video on a single date picker. I have implemented this solution in my dashboard but I have a slight problem. I need the single date to default to the current date...but because I'm using the date 'After', it defaults to the minimum date in my calendar table. Do you have some suggestions on how to implement this solution but to default to the current day. For added context...I'm using the single date picker to filter a table for records between a Start Date and End Date column.
Thanks Patrick, that’s definitely useful for end users who don’t want/need to learn how to use the application.
How easily could these be changed to DLY, WTD, MTD, QTD, YTD?
Can the _selecteddate be passed to a filter to create YOY comparisons?
Complete PowerBi noob here :)
In terms of DLY, WTD, MTD, QTD and YTD - it's just DAX. With regards to using the selected date, that may not be usable directly with the time intelligence functions, so you may need to manually write it out. It's not impossible though.
Hi Patrick! THank you very much for the video. It seems like the sample link is no longer working :( would it be possible to reupload it?
Awesome trick patrick ;)
It always amazes us the ways you can accomplish things within Power BI. so many different tricks and what a little tinkering can do.
Amazing
The download link is wrong.
Gosh, you're smashing it thank you very much as always :P , but, if you put just the before or after on a slicer, its return the same, isn't it? which one is more affordable?
Choosing before or after just determines if you use MIN or MAX on the dates to get the selected date.
nice trick!! I would love the "treatas" video...
A few questions.... how do you make a table with the "measure icon" and how do you create the folders in that table?
Regards from Argentina!
here you are! radacad.com/organize-power-bi-dax-measures-in-folders
Very good!
MS should make this a built-in feature
I looooove you guuuuyssssss
Tricky, but it works. Thanks.
Thank you for the video, Patrick!
I need to do a similar thing, but I need to choose only month/year, without the day.
And also the end date (month/year).
The beggining and the end date (month/year). Can you help me, please??
this is super cool :-) nice trick
Awesome! Appreciate that 👊
Hi thank you for this solution.
But with powerbidesktpRS, if we published on report server, the "after" slicer had problem. We can select a date before the lastest choice.
Really useful. I used it to create a slider for growth instead of a date picker. However, when you click on the slider it brings the slider 'in-front' of the rectangle used to hide the 'to box'. Any tips for preventing this from happening?
Hi Patrick, great videos btw. I have an extended problem, I need to pick this date selected and first filter my transaction data where this selected date is between a from date and to date, and also use this to calculate difference from a due date in my transaction data (direct query), and then calculate aging buckets from that date difference. Let me know if you some ideas around this. I am trying to do this by calculating measures, and then putting an if statement, but that is a very tedious job.
This is Game changer✌️
Hey Patrick
Can you also show how can we show the selected value in date picker inside card.
Hi Patrick,
This works for visuals that uses summarized columns. consider something like a table which has to e filtered by picking a single date. Any ideas on how to achieve this?
When using a Slicer for Fiscal Year that has Year, Quarter and Month capability. How do we make sure the current Month is always presented when end-user opens the reports. Currently we publish and set the Month but I need the month to continue to move forward from month to month. On Aug 1st when I review the report it should be set to August 2020, On Sept 1st the filter should be set to September. I hope this makes sense?