Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 cycle(Image below)

 

 

 

My use case is:

Invoice NumberDue DatePayment DateInvoice DateInvoice Creation DateCycle Date
0392/28/20222/28/20222/18/20222/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's avatar
    tamerj1
    3 years ago

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

     

     

6 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

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

    MonthMonthNoCycleDate
    Jan1Cycle11/4/2022
    …   

     

    then everything become easier.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

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