First / Last Date on my date table

My date Table brings all dates between the first and last invoice of a table “DsPayableinvoice”.

= List.Min(DsPayableInvoice[InvoiceDate],Date.From(DateTime.LocalNow()) )
= List.Max(DsPayableInvoice[InvoiceDate],Date.From(DateTime.LocalNow()))

Unfortunately, some clients found a way to add invoices with years like 7660

This means my date table is too big and we run out of memory while trying to refresh the data.

Could someone help me to still use this logic but limit the first invoice and last invoice to let’s say:

The last invoice is no further than let’s the current year.
First invoice: no older than 10 years, as my older client is 10 years old.

Thanks
Eze

Hi @ezenunez,

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.

I hope this is helpful

1 Like

Hello @ezenunez ,

Did the response above from @Melissa solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need? If it did, please mark the solution that helped you.

Thank you

Hello @ezenunez ,

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.

Thank you