Creating a column verifying whether the numbers create a numerical sequence

[akslagzim]

Hello,

I have a data which are divided by group (A, B, C). Number of rows for each group can equals from 1 to 8. Every row is defined by column "Number". I need to create a column that will be verifying if the numbers in each group are one by one.

Example,

In group A every number is one by one - OK.

In group B number 20240506 is not after 20240504 (20240505 is missing) so for all rows in group B the column will return the result "NOK".

The same situation in group C.

Group	Number	Result
A	20240801	OK
A	20240802	OK
A	20240803	OK
B	20240503	NOK
B	20240504	NOK
B	20240506	NOK
C	20231000	NOK
C	20231001	NOK
C	20231010	NOK
C	20231011	NOK

I was thinking to define MIN number in each group and after that verify if in column appears number +1, +2, +3 etc. but I have problem to wirite this... and maybe there is another way to do this.

Any guidance you can provide would be invaluable.

Thank you in advance 🙂

[akslagzim]

Wow, simple and effective, thank you very much

[johnt75 · Accepted solution]

Try

Result =
VAR _Min =
    CALCULATE ( MIN ( 'Table'[Number] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR _Max =
    CALCULATE ( MAX ( 'Table'[Number] ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR _NumRows =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Group] ) )
VAR Result =
    IF ( _Max - _Min = _NumRows + 1, "OK", "NOK" )
RETURN
    Result