One question I got from the Enterprise DNA forum revolves around an addition to the date query table. This addition prevents you from having to manually filter data to the last date every time new data comes in. This can be helpful if you’re working on an ongoing report. You may watch the full video of this tutorial at the bottom of this blog.
I’ll add a link to the said post on the related support forum posts below.
Note that the solution I’m giving will require the use of the old date table template I gave in the Ultimate Beginner’s Guide to Power BI and the Ultimate Beginner’s Guide to DAX courses. The links to these are also found below.
The template is a text file that needs to be placed in the query editor. It generates a date table based on the date parameters that you put in.
Once that’s in place, I’m going to show you how to make the table dynamic based on the actual data you have.
Creating A Dynamic Date Table Using Live Data
First, I’m going to check the overall dates in the Sales table.
The Sales table will serve as the fact table as we try to get the Overall Min Date. I’m going to use the CALCULATE function and then use the Order Date.
Note that I also have slicers here, so I need to use ALL Dates. This removes any filters that could be bringing in the wrong context into the measure.
Now, I just need to recreate this measure. So I’ll copy this formula and click on New Measure.
This new measure will be for the Overall Max Date, so I just need to change the MIN to MAX.
Let me drag these measures into my report to see what dates will come up.
When I look at the data from the Sales table, the first date in my timeline falls at the start of 2014 while the last date falls in mid-November of 2018.
Dates Query Parameters
Since the main goal here is to make the Dates table dynamic, the resulting dates would be somewhat different from the cards you previously saw, especially once we change things in the Query Editor.
I’m going to start by getting into the Dates Query.
Then, I’ll go into the Advanced Editor.
This is where you’ll see a text file where you can personally input the dates covered.
This is now going to generate the entire Date table code based on what we place in the StartDate, EndDate and FYStartMonth.
It’s this Dates Query that generated the table below.
Creating The Dynamic Date Table
What I’m going to do now is to duplicate the previous table and use this duplicate to start a Dynamic Date table.
Normally, you would go to the Source to generate the data.
Then, you can change the date right here in the Dates Query.
But what I’m going to show you is how to overlay it instead.
I’m going to start off with List Min. My Sales table remains my fact table, then I’m going to reference the Order Date.
The second part of the measure will be the List Max, and will also use the Sales table and the Order Date.
With these changes in the measure, the table will now automatically update every time new data comes into the Sales table. The Date table will also adjust according to the relevant dates.
Of course, you also need some room for adjustment. This is why I usually add around 30 days to the last date. That’s what this last part of the measure is for.
This is an interesting concept that would help you run any kind of scenario analysis. It’s better to add some extra time in the end instead of just cutting it off at the last date.
Once we go back to the cards that show the first and last dates, you’ll see that the last date has already been adjusted.
***** Related Links *****
Build A Comprehensive Date Table In Power BI Really Fast
Show Results Up To Current Date Or A Specific Date In Power BI
How To Work With Multiple Dates In Power BI
Conclusion
With a dynamic date query table, there’s no need to manually filter the dates each time new data comes into your fact table.
Again, I always prefer to create date tables in the query editor because it’s still the most efficient way to do it.
You’re going to need the text code that you can download from the Ultimate Beginner’s Guide courses for that, so check out the link to those courses below.
All the best,
Sam
[youtube https://youtu.be/qdhsq23cQhg?rel=0&w=784&h=441]
