In this tutorial, I’ll show you a very specific technique where you can show data or filter up to the current date (today) or any specific date in your Power BI reports. I’ve seen this asked a few times, especially on the Enterprise DNA Support Forum. I’ve personally used this technique many times, so I know that it’s both easy and useful. You may watch the full video of this tutorial at the bottom of this blog.
The reality is that the solution for this is much simpler than you may think. All you need to do is understand where the key locations are to set this up correctly.
Having a solid understanding of data modeling and why you need to set up your data model effectively is important here.
Sometimes, you may have calculations or a way of representing your data but they go out of the time range or time window that you want to highlight. To show a specific date or the current date in your Power BI report, you need to create a calculated column in your Date table, and then use it as a date filter to change the way a visualization represents data.
IF Logic To Show Specific Date
The data in this example shows results for every single day. You’ll see here though that my Date table goes all the way out until 2019. This would be very common if you’re using a Date table that has quite a wide range.
In this example, I wanted to have my Date slicer go up to the very last day of the data or today. So, I wrote a logic with a sort of date filter that will update daily. Whenever I look at this report, this will always represent the current date. I’ve created the formula within this column, which is inside my Date table.
I created a switch here, Is Before Today. This IF logic states that if the Date is less than or equal to Today then it’s equal to True and equal to False.
This is a very simple logic that is similar to what you would do in Excel. From here, I added this to my slicer, which then filters up to today.
Showing The Current Date In The Slicer
Filtering up to the current date is made really easy in Power BI because all I needed to do was find the switch that I had just created, Is Before Today, and drag it into the filter area. I put it as a Page level filter here.
Then, I selected True because that logic is going to equal to true if the day is before today.
With that in the filters, you can see that the slicer is now showing the current date that I’m recording this, which is 18th of July 2018.
Now, the slicer only goes up to that particular date.
***** Related Links*****
Show Days Before Or After A Selected Date Using Power BI
Month to Date (MTD) To Today’s Actual Date In Power BI Using DAX
Build A Comprehensive Date Table Really Fast in Power BI
Conclusion
How to show current date in Power BI is a frequently asked question, which is why I decided to go over it in this tutorial.
What’s great about this technique is that you don’t need to update your Date table every single day. If you have a detailed and lengthy Date table, where you could go up to 2020 or even further, place the switch in your Date table. It enables you to place a filter on either a page or your entire report only up to the current date. This is a great time-saver for you.
This is a combination of visualization techniques with some simple DAX formula (in a calculated column). Once this has all been applied to your reports, you’ll likely see how seamlessly it while adding value to your visuals at the same time.
Enjoy this one!
Sam
[youtube https://www.youtube.com/watch?v=veSDFbjBJXg&w=784&h=441]
