Forum Discussion
Helper IVDate Table Power Query adjustment for End Date
Helper IV
That 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?
Super UserLook 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?
- lbendlin
Keep in mind that fiscal years often do not end on 12/31. So no, it's not required. But it won't hurt either.