Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
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
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 ?
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.
Here you go 😊!
Let me know if you have any more questions 😊
Uhmm, well you do have an incremental number so you can do something like this in power query:
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 ?
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |