Field Chronological order

[eric98]

Hi, 

I have an issue and you may help with it. 

Let's suppose I have a table of invoices like this :

invoice_number	client_name
22012304/OCS	COCA
22012305/OCS	COCA
22012306/OCS	COCA
22012307/OCS	DELL
22012309/OCS	DELL
22012310/OCS	DELL
22012311/OCS	HP
22012312/OCS	HP
22012314/OCS	HP

Invoices number have to be in chronological order.  You can see that invoices number 22012308/OCS and 22012313/OCS are missing. 

Do you know how to detect if the chronological order is break or not ? Or even if it possible to do it in Power BI.

Thanks for your help.

[v-jayw-msft · Accepted solution]

Hi @eric98 ,

Please check the formula:

is break =
VAR last_invoice =
    CALCULATE (
        MAX ( 'Table'[invoice_number] ),
        FILTER (
            'Table',
            'Table'[client_name] = EARLIER ( 'Table'[client_name] )
                && 'Table'[invoice_number] < EARLIER ( 'Table'[invoice_number] )
        )
    )
VAR last_number =
    LEFT ( last_invoice, 8 ) + 0
VAR current_number =
    LEFT ( 'Table'[invoice_number], 8 )
RETURN
    IF ( last_number = 0, 0, IF ( current_number - last_number = 1, 0, 1 ) )

Best Regards,

Jay

[NickolajJessen]

Correct

---

So I would make the addition of the first element with the index.

And I would be able to have where the chain became broken.

---

Not with this setup. is that something you need as well?

Should I be able to know how many chain are broked by count them ?

---

[eric98]

Yeah I would like to have it.

I'm thinking instead of the text "good so far" and "chain broken", I would use "1" for "chain broken" and "0" for "good so far", like that I will just make the sum of "1"

But like you said, with this setup, the result are not good.

[NickolajJessen]

Here you go 😊!

Let me know if you have any more questions 😊

[NickolajJessen]

Uhmm, well you do have an incremental number so you can do something like this in power query:

[eric98]

So I would make the addition of the first element with the index.

And I would be able to have where the chain became broken.

Should I be able to know how many chain are broked by count them ?