Date sequence validation for duplicates

[Syndicate_Admin]

Hello everyone

I appreciate if you can help me with this consultation. I have the following table:

I need to perform the following validations:

- check if there is duplicate data in the "Order" column since it is with this data that I will work.

- once identified, validate that the number of the "Invoice" field whose "Status" field is "FILED" is less than the second record

- After that, validate that the "Cancellation Date" of the line indicating "FILE" of the duplicate pair is later than the "Date of issue" of the same line and that, in turn, it is prior to the date of issuance of the second record (the one with the invoice number greater than the duplicate pair. consider date and time.

- if this sequence is correct, indicate OK in the "Result" field of the line in which the "Status" field indicates "FILE".

- if incorrect, indicate "Error".

- For orders that are not duplicated, leave the "Result" field blank

Finally, create a calculated measure that counts how many "Errors" there are in the column, without counting blank spaces.

The result would be something like this:

I hope you can help me.

Thank you so much!

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhmPkQZ6EPjMyKyuS?e=iVbqzL

[Syndicate_Admin]

Thanks for the reply. On the result, this OK, however I have left another condition: - there may be records with cancellation date but that does not have duplicate the order. in this case, it should also be OK. Please your help. Thank you.

[Ahmedx]

show me an example so I can understand you

[Syndicate_Admin]

Hello, thank you. Attached example table. In red the records that are with FILE status but that do not have duplicate order, but that should appear as OK.

An additional query, this columa result, can be added to the table and not as a measure? in that case, the other fields that are blank, could be with OK? This would be easier for me to be able to count the errors and show them in a label in the visualizations.

Thanks a lot!

Invoice	State	Order	Issue date	Cancellation date	Result
235-2023-000446	Regularized	000172844030	30/01/2023 21:25:43
235-2023-000095	LEGAJADA	00172843960	6/01/2023 22:50:15	6/01/2023 23:16:37	OK
235-2023-000027	LEGAJADA	014494368470	3/01/2023 18:29:02	3/01/2023 21:53:35	OK
235-2023-000026	LEGAJADA	014494368514	3/01/2023 18:29:02	4/01/2023 18:24:54	Error
235-2023-000046		014494368514	4/01/2023 18:23:30
235-2023-000185	LEGAJADA	014494369015	13/01/2023 00:19:03	13/01/2023 00:22:56	OK
235-2023-000201		014494369015	13/01/2023 19:39:18
235-2023-000186	LEGAJADA	014494369026	13/01/2023 00:19:03	13/01/2023 00:22:57	OK
235-2023-000202		014494369026	13/01/2023 19:39:18
235-2023-000200		014494369041	13/01/2023 19:39:18
235-2023-000249	LEGAJADA	014494369170	17/01/2023 18:46:53	19/01/2023 11:22:15	Error
235-2023-000271		014494369170	18/01/2023 18:30:04
235-2023-000248		014494369181	17/01/2023 18:46:53
235-2023-000247	LEGAJADA	014494369214	17/01/2023 18:46:53	18/01/2023 11:22:14	OK
235-2023-000268		014494369214	18/01/2023 18:30:04
235-2023-000437		014494377301	30/01/2023 19:20:42
235-2023-000436	LEGAJADA	014494377312	30/01/2023 19:20:42	30/01/2023 20:54:04	OK
235-2023-000438	LEGAJADA	014494377323	30/01/2023 19:20:42	31/01/2023 12:18:38	OK
235-2023-000440	LEGAJADA	014494377334	30/01/2023 19:20:42	31/01/2023 12:18:39	OK
235-2023-000442		014494377382	30/01/2023 19:20:42
235-2023-000439		014494377404	30/01/2023 19:20:42
235-2023-000441		014494377415	30/01/2023 19:20:42
235-2023-000118	LEGAJADA	099612220552	9/01/2023 00:00:00	9/01/2023 22:50:58	OK
235-2023-000119		099612220552	9/01/2023 22:55:49
235-2023-000138	LEGAJADA	099612220563	10/01/2023 22:21:58	11/01/2023 13:23:23	OK
235-2023-000157		099612220563	11/01/2023 22:47:01

[Syndicate_Admin]

Hello, please confirm if you could review it? Thank you!

[Ahmedx]

check the solution!
https://1drv.ms/u/s!AiUZ0Ws7G26Rhm76ey3HyQv2GwHz?e=I6K8C5

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Syndicate_Admin]

Hello, thanks for the clarification.

I attach the requested detail:

Invoice	Order	State	Issue date	Cancellation date	Result
235-2023-000478	000173751473	LEGAJADA	01/02/2023 19:23:22	01/02/2023 19:27:02	ok
235-2023-000481	000173751473	Regularized	01/02/2023 22:27:27
235-2023-000026	014494368514	LEGAJADA	03/01/2023 18:29:02	04/01/2023 18:24:54	Error
235-2023-000046	014494368514	Regularized	04/01/2023 18:23:30
235-2023-000176	014494368993	Regularized	12/01/2023 18:47:41
235-2023-000181	014494368993	Regularized	12/01/2023 18:47:41
235-2023-000185	014494369015	LEGAJADA	13/01/2023 00:19:03	13/01/2023 00:22:56	Ok
235-2023-000201	014494369015	Regularized	13/01/2023 19:39:18
235-2023-000186	014494369026	LEGAJADA	13/01/2023 00:19:03	13/01/2023 00:22:57	Ok
235-2023-000202	014494369026	Regularized	13/01/2023 19:39:18
235-2023-000204	014494369030	Regularized	13/01/2023 19:39:18
235-2023-000249	014494369170	LEGAJADA	17/01/2023 18:46:53	19/01/2023 11:22:15	Error
235-2023-000271	014494369170	Regularized	18/01/2023 18:30:04
235-2023-000247	014494369214	LEGAJADA	17/01/2023 18:46:53	18/01/2023 11:22:14	Ok
235-2023-000268	014494369214	Regularized	18/01/2023 18:30:04
235-2023-000438	014494377323	LEGAJADA	30/01/2023 19:20:42	31/01/2023 12:18:38	Ok
235-2023-000466	014494377323	Regularized	31/01/2023 23:03:30
235-2023-000821	014494394521	LEGAJADA	21/01/2023 18:38:23	22/02/2023 16:16:00	Ok
235-2023-000840	014494394521	Regularized	22/02/2023 17:12:41

In addition to this, the calculated measure of how many errors there are. In this case, it should show 2 on the visual card I will place.

Thanks again.