Try something like this:
To demonstrate, create this query and call it FactTable
let
Source = Table.FromColumns(
{
List.Dates( #date( 2000, 1, 1 ), 365*24, Duration.From(1) )
}, type table [Date = date]
)
in
Source
This exceeds the limits you’ve described
Now create these Queries as well (you can disable load for them all)
Query name = FistEverDate
let
Source = #date(2012, 1, 1)
in
Source
Query name = LastEverDate
let
Source = Date.From( Date.EndOfYear( DateTime.FixedLocalNow() ))
in
Source
Query name = CalendarStartDate
let
Source = List.Max( { FistEverDate, List.Min( FactTable[Date] ) } )
in
Source
Query name = CalendarEndDate
let
Source = List.Min( { LastEverDate, List.Max( FactTable[Date] ) } )
in
Source
Note that you if you do not want to create these as separate queries, you can add them as steps to an exsisting query but in my view this makes it easier to review.
All that’s left is refer to the CalendarStartDate and CalendarEndDate in your Dates table function query.
Due to the length by which this post has been active, we are tagging it as Solved.
Thanks to the contributors of this post.
For further questions related to this post, please make a new thread. Please feel free to reopen this thread if anyone would like to answer the pending inquiry above.