Forum Discussion
Helper IVDate Table Power Query adjustment for End Date
Super UserThe calendar table has to be contiguous, and it has to cover both the fact dates and the result of the time intelligence calculations. So cutting off future dates is fine unless you try to do stuff like "next month's data".
dbrandoneThat is what I am finding. I didn't want to set the date table to a specific max year since in the future that max date may come up so I tied max date to a specific column in a table that deals with future orders. The problem is that it seems some time intelligence calculations ar off. I was wanting to see if there is a mixture of the two to keep the max date fluid, but also taking the max date in the specific column and taking it to the end of the calendar year. I feel comfortable with Dax, but with power query I am still relatively new.
any ideas on power query code to take the max date of a column and taking it to the end of the year?
- lbendlin
Look at this from a different perspective: If you disable auto date/time (as you should) then you save much more space in the data model than what you spend by specifying a fixed calendar that covers a couple of years in the future (say, until December 2023).
What you don't want to do is a CALENDARAUTO() on a date range from 1/1/1900 to 12/31/9999 (yep, seen that more than once, and the results are horrifying)
- dbrandone
Absolutely. I definitely do not want to have many years in the future as part of my date table but for example, if my max date in the table column is 11/17/2021, then would my time intelligence work better if the power query then took the max date to 12/31/2021 since that is the last date in the year from the max date in the table column. Once a date in 2022 is entered into the table column, then the date table would go to 12/31/2022.
the only reason I am wondering is seeing if having the max date as not 12/31 of a specified year, would this throw off say Startofyear time intelligence?