Relative date filter to invoice entries

Hi NandoBI ,

Here is my sample data.

You need to create these new columns.

rank =
RANKX (
    FILTER (
        'Database$SalesInvoiceLine',
        'Database$SalesInvoiceLine'[Sell-to Customer No_]
            = EARLIER ( 'Database$SalesInvoiceLine'[Sell-to Customer No_] )
    ),
    'Database$SalesInvoiceLine'[Posting Date],
    ,
    ASC,
    DENSE
)

previous date =
CALCULATE (
    FIRSTNONBLANK ( 'Database$SalesInvoiceLine'[Posting Date], 1 ),
    FILTER (
        'Database$SalesInvoiceLine',
        'Database$SalesInvoiceLine'[Sell-to Customer No_]
            = EARLIER ( 'Database$SalesInvoiceLine'[Sell-to Customer No_] )
            && 'Database$SalesInvoiceLine'[rank]
                = EARLIER ( 'Database$SalesInvoiceLine'[rank] ) - 1
    )
)

result =
VAR a =
    CALCULATE (
        LASTDATE ( 'Database$SalesInvoiceLine'[Posting Date] ),
        ALLEXCEPT (
            'Database$SalesInvoiceLine',
            'Database$SalesInvoiceLine'[Sell-to Customer No_]
        )
    )
RETURN
    IF (
        DATEDIFF ( a, TODAY (), DAY ) > 60,
        TRUE,
        IF (
            'Database$SalesInvoiceLine'[previous date] <> BLANK ()
                && DATEDIFF (
                    'Database$SalesInvoiceLine'[previous date],
                    'Database$SalesInvoiceLine'[Posting Date],
                    DAY
                ) > 60,
            TRUE,
            FALSE
        )
    )

Then use filter to display your results.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.