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.

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.

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.

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.

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

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.

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

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

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

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.

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.

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

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

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

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.

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

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!

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.

Love you Patrick from Pratik for this Trick!

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.

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 loved the solution. Thanks!

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

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.

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.

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

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

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.

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

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

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

Thanks a lot!

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.

Please make up a video on treatas, thanks!

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

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.

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

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

I wish Power Bi just had a date picker already! And a default calendar table! Come on, Power BI!!

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.

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

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

Amazing !

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

Great Stuff !

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

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

Nice video, love your enthusiasm, Patrick!

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

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

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

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

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.

Nice clear video

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!

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

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?

Bro that was awesome. Thank you

Hi Patrick, I just wanna say ur awesome

My Man!!!!!!!!!!!!!!!!!!!!!!!!

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

Thanks for making BI funzy

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.

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

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.

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!

quite insane that there isn't an 'equal to' option in the date slicer in the first place!

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.

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

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?

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!

The download link is wrong.

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

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.

Amazing

I looooove you guuuuyssssss

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.

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.

thanks! cool workaround.

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

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.

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

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 ?

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

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

Tricky, but it works. Thanks.

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

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.

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

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?

Guy in the cube, I have a challenge for you and it revolves all around hierarchies

This was masterpiece

Nice clean DAX - it's a pity that it has to be done so many times around the report

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

Awesome trick patrick ;)