Find Nearest Date in a Calendar

[yagoframos]

I've a data base, for Invoices paid on the last few months. I've Invoice Date, Invoice Creation Date, Invoice Due Date and Invoice Payment Date. In other table I've a calendar with Dates based on a cycle(Image below)

My use case is:

Invoice Number	Due Date	Payment Date	Invoice Date	Invoice Creation Date	Cycle Date
039	2/28/2022	2/28/2022	2/18/2022	2/28/2022	?

In summary I need to find the closest date related to my "Due Date". In that example I'll use Cycle 9, Cycle date 2/28/2022.

[tamerj1]

Hi @yagoframos 
Assuming that you are creating a calculated column and that the Calendar table is unpivotted as suggested by @FreemanZ then you may try the following

Cycle Date =
MAXX (
    TOPN (
        1,
        FILTER (
            'Calendar',
            'Calendar'[Month Number] = MONTH ( PaidInvoices[Due Date] )
        ),
        ABS ( PaidInvoices[Due Date] - 'Calendar'[Date] ), ASC
    ),
    'Calendar'[Date]
)

[yagoframos]

Awesome!!! That works for the next Cycle, and if I want to see the first one before the due date?

[tamerj1 · Accepted solution]

Hi @yagoframos 
Please find attached sample file containing both options.

Cycle Date 2 = 
MAXX (
    TOPN (
        1,
        FILTER (
            'Calendar',
            'Calendar'[Month Number] = MONTH ( PaidInvoices[Due Date] )
                && PaidInvoices[Due Date] - 'Calendar'[Date] >= 0
        ),
        PaidInvoices[Due Date] - 'Calendar'[Date],
        ASC
    ),
    'Calendar'[Date]
)

[yagoframos]

Thank you!!!

[FreemanZ]

hi @yagoframos 

the point might be to unpivot your table first to like:

Month	MonthNo	Cycle	Date
Jan	1	Cycle1	1/4/2022
…

then everything become easier.

[yagoframos]

That should't be a problem, my only concern is the best way to use the LOOKUP to find the right date.