Forum Discussion
Find Nearest Date in a Calendar
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 c...
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] )
tamerj1
Community Champion
Community ChampionHi 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]
)Awesome!!! That works for the next Cycle, and if I want to see the first one before the due date?
- tamerj1
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] )Thank you!!!