Comparing a sequence number to a date field

[Anonymous]

I have a table with a sequence order based on the order a supplier sends in shipping notifications and I have a date column based on a receipt date of material. I am trying to see if the receivers are receiving in the correct order based on the supplier's sequence. This example is what I would expect to see, each sequence has a progressively later date. How would I go about writing a query to check for failures?

SEQUENCE	DELDATE
0008	1/15/2025	Are the dates in the order of the sequence?
0009	1/31/2025
0010	2/7/2025
0011	2/13/2025
0012	2/20/2025
0013	2/25/2025

[Anonymous · Accepted solution]

Hi @Anonymous,

Thanks for the reply from Greg_Deckler.

You could consider creating a measure to check if the dates in the order of the sequence:

DateOrderFailure = 
VAR CurrentSequence = MAX('Table'[SEQUENCE])
VAR CurrentDate = MAX('Table'[DELDATE])
VAR NextSequence = 
    CALCULATE(
        MIN('Table'[SEQUENCE]),
        FILTER(
            'Table',
            'Table'[SEQUENCE] > CurrentSequence
        )
    )
VAR NextDate = 
    CALCULATE(
        MIN('Table'[DELDATE]),
        FILTER(
            'Table',
            'Table'[SEQUENCE] = NextSequence
        )
    )
RETURN 
    IF(
        NOT(ISBLANK(NextDate)) && CurrentDate > NextDate,
        "Failure: Date out of order",
        "Correct order"
    )

Then you could apply this measure to a table visual as above.

Reards,

Qi

[Greg_Deckler]

@Anonymous This measure will list any dates that are out of sequence.

Measure = 
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    'Sequence',
                    "__PrevDate", MAXX( FILTER( ALL( 'Sequence' ), [DELDATE] < EARLIER( [DELDATE] ) ), [DELDATE] )
                ),
                "__PrevID", MAXX( FILTER( ALL( 'Sequence' ), [DELDATE] = [__PrevDate] ), [SEQUENCE] )
            ),
            "__Diff", [SEQUENCE] - [__PrevID]
        )
    VAR __Result = CONCATENATEX( FILTER( __Table, [__Diff] <> 1 && [__PrevDate] <> BLANK() ), [DELDATE], ", " )
RETURN
    __Result