Build a DATE PICKER in Power BI Desktop

[@samtoulayl]

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.

[@meikelkrieger]

Totally agree!

[@Pier_Marco]

agree, especially that white box and slicer itself is another performance utilizator, great tip though !

[@roberttyler2861]

All this effort for a single date selection.

[@ameyraj4947]

Agree on this

[@sunny051488]

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!

[@agnespitka3703]

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.

[@akhilannan]

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.

[@markherd5398]

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.

[@dakinsvibe]

Had this exact request in the last couple of days, ideal. Thanks Patrick, super helpful as always!

[@GuyInACube]

Awesome! Glad it was helpful 👊

[@martinturcotte2371]

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.

[@tompaclock]

Martin Turcotte I know, right? You have to fudge EVERYTHING to get such simple functionality. It makes maintenance a nightmare.

[@marcgranger2563]

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

[@fl4ch2ange]

Couldn't agree more :-)

[@read89simo]

Which DAX class is that ? Thanks

[@BritonWells]

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

[@GuyInACube]

love it! Always a give and take. As you call out - best to understand the ramifications and then make a decision on best course.

[@rafaeldonado]

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.

[@GuyInACube]

ohhhhh nice. Will have to give that a try. Thanks for that! 👊

[@sonetmollick4630]

It works but then date slicer looks very tiny.

[@lovenisarg]

Hi, Where would you find responsive option for the slicer. I tried to check but couldnt find.

[@TheGreyFatCat]

great content as always - however why is this not a standard feature already?!

[@fl4ch2ange]

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.

[@GuyInACube]

Would be great if it was there out of the box.

[@fl4ch2ange]

@@GuyInACube BTW: you guys rock!

[@andres777video]

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

[@LinqLover]

Hi Andreas, it would be great if you could share your visual in any way - GitHub, PM, whatever you like :-)

[@danielserra5340]

Could you please share it, I need that for my reports.

[@adityaupadhyay3242]

Hey Patrick nice video. I think the rectangle shape should be moved to front for foolproof functionality. What do you say?

[@th1493]

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!

[@AnalyticswithNags]

he he awesome . This is absolutely a workaround. Suits well for small projects. Project with 100's of Measures will be difficult to maintain.

[@LuanNguyen-pl2wf]

Patrick explains it like im a dummy. and to be honest. i love it !

[@frehiwotfitamo7548]

I am at a loss for words, It is marvelous. Appreciated

[@pamknight1304]

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.

[@smanzoli]

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!

[@Javilanderos]

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.

[@xihan9513]

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.

[@samirsahin5653]

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.

[@shashankgupta8985]

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.

[@bandarfalah8206]

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.

[@PallaviChauhan91]

Yes, that's so much easier.

[@РоманКолебанов-д3э]

Man, so funny, so powerful! Subscribed. Thank you!

[@GuyInACube]

Thanks for watching! 👊

[@chaithanyavck5108]

@Great tip for Date Picker, is there any chance to set default slicer value to Today always and date picker?

[@TTfromOZ]

Great trick! needed it some time ago. But I am sure I will need it again!

[@jonsnowboard]

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.

[@TeresaJouben]

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.

[@amaraokeke6192]

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.

[@MrKhulaid]

Would you be able to do a video including the same thing but with a hour picker, similar to the filter pane ?

[@jordanhelus9598]

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!

[@michaelkraithat4852]

Oh dam! So simple but so good. This is perfect. Your a dude!!!! ✊🏽

[@GuyInACube]

Has helped us out for sure! 👊

[@ArifKhan-qo9jw]

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.

[@lohannc]

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.

[@alexxpinheiro]

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

[@nishukumar3886]

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

[@GuyInACube]

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.

[@mrbilbao01]

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

[@pratikraval44]

Love you Patrick from Pratik for this Trick!

[@mariajuliaseveriano4337]

Hi Patrick thanks for your video. it really helps but I have got an error saying " a single value for the column "sales" in the table can not be determined. what am I doing wrong? Cheers

[@aadhavsundar2869]

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?

[@rahuldesai7777]

I have a similar requirement, did you find a solution to this?

[@ThePilli41]

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?

[@NicotHoen]

Great Patrick Gonna use this one, a bit differently. I am gonna use this as a time travel

[@karanamsaisuprajarao8884]

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

[@MsExcelpro]

Hi patrick. i'd be interested to see the implementation of Calculation Groups for similar Measures

[@GuyInACube]

That would be interesting to look at. Thanks for the suggestion 👊

[@boomshotta]

Is there any way to make it so the shape that covers the date picker we don't want doesn't disappear when you select from the date picker we do want? I already have the layers set correctly.

[@kristalittle5676]

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?

[@montalbaconsulting5871]

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.

[@pavankumar-jm9ic]

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

[@p3c1100]

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 ?

[@joaquinmaverick82]

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!

[@piotrsabak3558]

here you are! radacad.com/organize-power-bi-dax-measures-in-folders

[@reesourcesinc]

Thank you so much!! I had this problem weeks ago.

[@GuyInACube]

Glad this is useful for you! 👊

[@vaishnaviajmera8796]

Hey Patrick thanks for the video! But can you tell how can I select by default current date in this filter.

[@dmatthieu2]

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 🤷🏽‍♂️

[@driouchemountasir7894]

Hi Patrick, awesome video as usuall !! Love that kind of tips. You guys from GIAC help a lot. Thanks for that

[@codelemon4327]

Shouldn't this be called Guys in a Cube? I greatly appreciate both of your work.

[@ameyraj4947]

Hey Patrick Can you also show how can we show the selected value in date picker inside card.

[@guillermochez5531]

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.

[@kevinbecker3436]

hi patrick i have a question to drills a custumer we have has the following hierachie in his database custumer-order-position-process and he wants to drill down all of these no problem but when he is drilled down to the orders of a single custumer he just sees the orders but not from which custumer they where do you have a solution for this? Example: Custumers: disney (orders 1,2,3), warner (orders 1,2), sony (orders 1,2,3,4,5,6) now he enables drill down and clicks warner and all he sees is 1 and 2 but not warner 1 and warner 2

[@luisalejandrorodriguezcamp9516]

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!!

[@safeeruddin2927]

Please make up a video on treatas, thanks!

[@TeresaMiddleton-nt9nd]

Hi, Patrick. I asked this question before I joined the channel...so asking again. 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.

[@GuyInACube]

We have an idea but quick question, are you only displaying today’s data on the page with the slicer? Can you provide more details?

[@TeresaMiddleton-nt9nd]

All of my visuals will display the data for the date selected by the slicer. It is defaulted to the minimum date (I have data for 2 years), but I would like it to show the max date (which should be the current day) when first opening the dashboard. One of my visuals is a table of employee data and each row has a Start Effective and End Effective date. I need the table to filter to only the rows where the selected date from the slicer is between the Start and End Effective dates of the rows in the table. I managed to get the single date slicer to work to filter the table but I haven't figured out how to default the slicer to the max date.

[@bhavinsolanki5615]

I loved the solution. Thanks!

[@dmiradakis]

Nice video, love your enthusiasm, Patrick!

[@ch.bhanuprakash1148]

Hi patrick, small help from based on your video that I'm using single date picker as same way i need with that slicer selection i need to filter max date to 0,and 0 to 30 days, 31 to 60 days,61to 90 days, 90 to till min dates plz help me some measures to filter when user select particular date. in slicer.

[@TarunYadavVlogs]

Hey man, i need your help very badly. I have a createdon column and want to filter data based on created on so I am using slicer to set start date and end date to filter the data but I need 1 more thing here, I want days difference between slicer start date and end date which is not happening because of createdon less data. Can you pl help??

[@nataliamedvedeva3413]

Thank you a lot! Your video helped me to find a decision of my problem

[@noorjannah5112]

I want to ask one question about date filter in page level. In one page I have "Last week", "MTD" , "YTD" and also "Daily Sales graph" which I want to show in current month. So, my date filter will reflect "MTD" and "Daily Sales Graph", so, I want to have YTD and LW as a scorecard which won`t reflect on my date filter. My issue is, I don`t find way on how to group "MTD", "Daily Sales Graph", and "date filter" and let YTD and LW scorecard as a stand alone. Can you make one video for it? Thanks in advance

[@angiekistil2917]

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.

[@kiriampg]

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

[@coreypoindexter8291]

I need help!! Im trying to create a custom schedule periods with different information every 4 weeks. Such as 4/16/23 to 5/13/23 with hospital unit staff levels as the data points. This information would repeat every 4 weeks.

[@oscarflorez-martinez5093]

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!

[@AbhayGadiya]

In single page i have few visuals which shows last three months data and other visuals show latest last month data. I am having problems filtering the data for latest last month. I have used relative date filters with last month (includes current) month, TOP 1 with month numbers, etc. but still not working. Could you please help. I always want to show the data in selected visuals for latest last month i.e. say i have uploaded July 2020 data then it should show July 2020 data. So in next month Aug-2020 till the time i upload the data for Aug-2020 it should keep showing July - 2020 data and once new data is uploaded it should show Aug-2020 data

[@nhuquynhnguyenngoc7040]

It’s really helpful. Thanks for your sharing. Best wish for you

[@OliverCortinas]

It's possible to store that value as a parameter and use that parameter in a query that refresh data in table?

[@WesamMuhammad]

Thank you for the video, the download sample link is not working

[@abhijitkatkar2869]

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.

[@alexisgarcia6950]

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?

[@ameyraj4947]

Helpful used the same in my project. But Power BI team should add this date picker visual in visuals as standard option

[@gennerjimenezvallejo8810]

"This is bananas"... Great Thaks Patrik.

[@vigneshr6660]

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

[@DroneEyes]

Great video Patrick ... how did you create a sub folder under the measures folder?

[@MidknightFooMan]

I watched this and then immediately got asked to do pretty much this by my boss

[@lizbellinghoven1840]

This is great! Just one simple question...why is a date picker not built in to PBI?

[@RonaldPostelmans]

Nice clear video

[@GuyInACube]

Appreciate that Ronald! Thanks for watching 👊

[@geraltrivia8580]

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

[@jedhansen4229]

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?

[@wwoojjtteekkpp]

Hi Patrick, thats is absolutly awsome :D it's a shame that PBI dosen't have a buildt-in option for a calendar picker

[@shanthipriyanka2775]

Hey Patrick, can you please help me with my problem, I have Between Date Slicer and be default, the todate should be set to today by default. I cannot use relative date here.

[@marounsader318]

Hi is the curly braces necessary, i remember i didnt use them and treatas working well, did i miss something?

[@aswinsp3083]

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?

[@violetastanosevic7503]

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 🙏

[@pokedigimaniaco]

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

[@mohtasimbillah3068]

hey patrick i have a request.As we know that drillthrough dosent works for filters that are inside any measures. Let say i have a measure: calculate(count(productA),filter(table,productA="Ship") + calculate( (count(ProductB),filter(table,ProductB="Mini"). Now when i click on drillthrough the data on target page is not filterd as per these measures So how we can achieve it if we have 5-6 such measures in a funnel chart to see only those exact records as for now only report,visual or column values are passed to drillthrough page

[@novak2033]

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

[@crazyinguiguitar42]

Hahahhaha you´re right about the forgetting part

[@sagarchhetri4531]

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?

[@albitron]

Were you able to achieve this?

[@visheshjjain]

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.

[@Elize91]

Hi Patrick! Struggling with something like this. I now want to use the date in my YTD formula. Companies have different fiscal year ending. I know I can fill in a YearEndDate, but I want the enduser to do this with the help of a calendar or parameter or something. So that the whole proces is automated. Can you help me?

[@1204shilpa]

I have a MTD metrics, how will this work for my scenario? Please suggest

[@gayathrireddy6639]

Hi I have a date picker for searching the data but the picker is not working for previous year. only picking the current year can you please guide me how to resolve this

[@rvweteling]

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

[@mahantikaruna4440]

I want the date picker values (with between option) to change as per another slicer for e.g. a current year. So, if "current year" is selected in one slicer, I want the date picker to show 1/1/2020 and 31/12/2020. Any ideas on how to do that?

[@LeonardSuares]

Bro that was awesome. Thank you