Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Comparing a sequence number to a date field

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?

SEQUENCEDELDATE 
00081/15/2025Are the dates in the order of the sequence?
00091/31/2025 
00102/7/2025 
00112/13/2025 
00122/20/2025 
00132/25/2025 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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