topic Re: Find Nearest Date in a Calendar in DAX Commands and Tips

Find Nearest Date in a Calendar

Anonymous — Tue, 21 Feb 2023 15:58:42 GMT

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.

Re: Find Nearest Date in a Calendar

FreemanZ — Tue, 21 Feb 2023 16:26:20 GMT

hi Anonymous

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.

Re: Find Nearest Date in a Calendar

Anonymous — Tue, 21 Feb 2023 17:03:19 GMT

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

Re: Find Nearest Date in a Calendar

tamerj1 — Tue, 21 Feb 2023 17:39:00 GMT

Hi Anonymous
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] )

Re: Find Nearest Date in a Calendar

Anonymous — Tue, 21 Feb 2023 19:30:35 GMT

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

Re: Find Nearest Date in a Calendar

tamerj1 — Wed, 22 Feb 2023 05:53:19 GMT

Hi Anonymous
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] )

Re: Find Nearest Date in a Calendar

Anonymous — Wed, 22 Feb 2023 17:48:50 GMT

Thank you!!!