Forum Discussion
Power Query max date from other table
Hi, I do exactly this in creating date tables in Power Query. Basically you create a reference to your original table, click on the Date column, then in the Transform tab, select the Date dropdown and select Earliest or Latest. It creates a scalar value.
You cannot use it as a parameter as a parameter dropdown, but if you create a filter, just tell the filter to day "date > Jan 1, 2020" then after the filter is created, change the #date(2020,1,1) to varStartdate (or whatever you called the value.
See my full article on the date table here, which has info on how to create these dynamic values to use in other places.
edhans , thank you for such a great blog post (Create a dynamic date table in Power Bi). I have long wanted to tackle this inconvenient problem. Thank you very much.
edhans , just a question. In your post you mention:
Make sure automatic date logic is off in Power BI. Select File, Options and Settings, Options, Data Load. Uncheck “Auto Date/Time”. Leaving this checked will create all sorts of unnecessary hidden tables and columns. You’ve created a perfect Date table. Don’t let Power BI’s AI mess this up.
So can I safely assume if I have created a "perfect" Date table, which all my other tables who have dates are connected to in a relationship, then I can always switch this off?
Yes michellepace .
You should always switch it off. I have my default to never have automatic date/time settings. Reasons:
- It creates a date table for every date column in your model. That is just a waste of space.
- It creates dates from the earliest to latest date. It should be from Jan 1 (or start of your year) to Dec 31 (or end of year).
- It creates dates on useless date fields. In the WorldWideImporters sample database from MS for example, there are at least 3 fields that have dates of Dec 31, 9999. This is because it is a product or program expiration date, and there is no mechanism to say "this program doesn't expire" so an impossible future date is entered. This creates a date table for each of these absurd fields with 2.9M rows each. I have seen this in the real world and just talked to user that sees this in their SAP databases.
- The date tables are super limited. Date, year, quarter, month. Not much else. No way to extend them, so you have to create your own anyway.
- SQLBI recommends in all of their books that reference Time Intelligence to turn this feature off.
I have never ever seen anyone give a compelling argument for using it vs a real date table. Ever.