Build a DATE PICKER in Power BI Desktop
ฝัง
- เผยแพร่เมื่อ 12 พ.ค. 2020
- 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/datepickersample
📢 Become a member: guyinacu.be/membership
*******************
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/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #GuyInACube - วิทยาศาสตร์และเทคโนโลยี
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 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.
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.
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.
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.
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!
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.
Nice video, love your enthusiasm, Patrick!
Thank you a lot! Your video helped me to find a decision of my problem
Great trick! needed it some time ago. But I am sure I will need it again!
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!
great content as always - however why is this not a standard feature already?!
I am at a loss for words, It is marvelous. Appreciated
Hi Patrick, awesome video as usuall !! Love that kind of tips. You guys from GIAC help a lot. Thanks for that
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
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.
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!
It’s really helpful. Thanks for your sharing. Best wish for you
Thank you so much!! I had this problem weeks ago.
Glad this is useful for you! 👊
Great Patrick Gonna use this one, a bit differently. I am gonna use this as a time travel
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.
Bro that was awesome. Thank you
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!
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.
thanks! cool workaround.
"This is bananas"... Great Thaks Patrik.
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.
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.
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!
Love you Patrick from Pratik for this Trick!
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.
Patrick explains it like im a dummy. and to be honest. i love it !
Great video Patrick ... how did you create a sub folder under the measures folder?
Great think .....
Is it possible to use the same solution to display not only a measure but a list of rows in a Table visualisation ?
Awesome stuff !
@Great tip for Date Picker, is there any chance to set default slicer value to Today always and date picker?
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?
Thanks a lot!
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
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.
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!
Please make up a video on treatas, thanks!
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
Amazing !
Great Stuff !
Oh dam! So simple but so good. This is perfect. Your a dude!!!! ✊🏽
Has helped us out for sure! 👊
Hey Patrick thanks for the video!
But can you tell how can I select by default current date in this filter.
Hi Patrick, its nice explanation.. is there any way we can select month alone with respective years without date in date picker..I want to implement it in my project..
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.
Hey Patrick
Can you also show how can we show the selected value in date picker inside card.
This was masterpiece
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.
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, I hope you can help me on my report, it involves picking a date and then based on that date, the report calculate the all the values before that date (previous). Thank you
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?
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
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!
Very good!
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 👊
Thanks for making BI funzy
Really cool video. Does this only work for Dates? I'm trying to mimic this for incident number, but can't figure that out.
Hi Patrick, I just wanna say ur awesome
Would you be able to do a video including the same thing but with a hour picker, similar to the filter pane ?
Amazing
Hey Patrick, i got a problem in my measures using sameperiodlastyear when i tried to change from a simple slicer of month and year to the slicer that you used (the between exactly). Some recommendations? I dont get how work with it at all
Hello Patrick, I need a solution for the below scenario.
A matrix table should contains two column of value (Amount) with selection of date option on each column respectively. I want to get a difference between the values appearing, bases on your selection of date in third column.
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?
Man, so funny, so powerful! Subscribed. Thank you!
Thanks for watching! 👊
Hi is the curly braces necessary, i remember i didnt use them and treatas working well, did i miss something?
Hi, thanks for awesome video👏
I have question for you. Is it possible to choose one month from list in slicer and see the following data in a matrix or table:
- rows: country
- value 1: sum of sales for chosen month by country
-value 2: sum of sales for previous month (chosen month - 1) by country?
I tried with measure PREVIOUSMONTH (), but value 2 is always Blank.
In rows have to be country, not a date.
Any help would be appreciated 🙏
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.
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?
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.
Niiiiiiice bananas :) thanks, will be implemented soon..
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
Hi patricj thanks for sharing your experience with us... I have a questiont that what is the use of rectangle shape in slicer at last of video
The relative date slicer always will give you the two dates listed. The rectangle was just to hide the greyed out date box. Helps with the display of a single date picker.
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??
Thanks for sharing! treatas video??
Hi Patrick, thats is absolutly awsome :D it's a shame that PBI dosen't have a buildt-in option for a calendar picker
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.
Tricky, but it works. Thanks.
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.
Nice clear video
Appreciate that Ronald! Thanks for watching 👊
Hi Patrick, Nice trick! I want to know can we pass a measure value in where clause of SQL query while importing the data from sql database to Power BI?
Were you able to achieve this?
Helpful used the same in my project. But Power BI team should add this date picker visual in visuals as standard option
This is Game changer✌️
It's possible to store that value as a parameter and use that parameter in a query that refresh data in table?
Thank you for the video, the download sample link is not working
Great Patrick, Please share a video how can I pick first and last employee salary data, where employee are repeated like all employees in Feb, Jan, May etc. but amount change all the times Or is there any trick get these. Waiting for your response.
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.
Hi guys, have a question..if i use a max ir min value for that date and use a filter on a visual or a table , how a prevent that the value on date change based on that filter ,and max ir min value now are include on this particular ? Ty
hey , is it possible to add time in sec on the y axis in power bi?
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.
this is super cool :-) nice trick
Awesome! Appreciate that 👊
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
Do you guys have a video on how to create a dynamic date slicer based on selected date type? For example, maybe there is a pending sales table with multiple date types - created date, sales follow up date, estimated job date, etc. with the Dates table linked to all of these dates but with only one default active - entered date. Is this possible? I have looked online in the different forums for this, but cannot find any help.
Basically need - date table linked to multiple dates in a pending sales table with the ability to filter/activate the data based on the selected date type in a slicer visual. Possible?
I have a MTD metrics, how will this work for my scenario? Please suggest