Count Ordernumbers base on duplicate value

[Applicable88]

Hello,

I have following table:

Ordernumber	Date	Status
8888	25.04.2021	A
7777	25.04.2021	A
1111	25.04.2021	A
1111	25.04.2021	D
2222	25.04.2021	A
2222	25.04.2021	D
3333	25.04.2021	A
3333	25.04.2021	D
4444	25.04.2021	D
5555	25.04.2021	D

Order numbers with D are the orders which we accomplished to ship out today already. Status A orders are the delayed leftovers from yesterday which "had" to become D later today. Thats the reason these orders have an "A" and an "D" counterpart. Thats the reason why orders with "A" only gets updated  once a day, otherwise they would be overwritten with "D" and you can't tell the real perfomance of today. "A" orders are not counting to the performance of today, because they should have finished yesterday.

So to calculate the performance of today istn't just count of D orders minus count of A orders. Its actually count all "D" orders minus the "D"orders which also has a "A" Status counterpart. 

In above sample data 2 should be the outcome. 

I hope it was not too confusing. 
Thank you in advance!
Best.

[OwenAuger · Accepted solution]

Hello @Applicable88 

A measure like this should work:

Performance = 
VAR Orders_D = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "D"
    )
VAR Orders_A = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "A"
    )
RETURN
    COUNTROWS ( EXCEPT ( Orders_D, Orders_A ) )

The logic is to take the set of Orders with Status D and remove Orders with Status A using EXCEPT. Any orders with Status A but not Status D will not influence the result.

With your sample data,

• Orders_D = { 1111, 2222, 3333, 4444, 5555 }
• Orders_A = { 1111, 2222, 3333, 7777, 8888 }
• EXCEPT ( Orders_D, Orders_A ) = { 4444, 5555 }

Regards,

Owen

[Applicable88]

Thank you again @OwenAuger .

I found the problem. My last function was not right. A simple sum was enough and afterwards it worked out.

Best. 

[Applicable88]

@OwenAuger ! 

Thank you very much it works for the ordernumbers. 

I forgot to mention that I have another column (Positions of deliveries) which this time needs to be sum up base on the above logic, so only sum these delivery positions of the 2 orders. I tried with calculate sum and sumx as well, but I always get an error or empty value and sometimes a much bigger number. As you can see I changed the last line to sum instead of count:

Performance = 
VAR Orders_D = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "D"
    )
VAR Orders_A = 
    CALCULATETABLE ( 
        VALUES ( Orders[Ordernumber] ),
        Orders[Status] = "A"
    )
RETURN
calculate( SUMX(Orders,Orders[Deliverypositions]), EXCEPT ( Orders_D, Orders_A ) )

    

Do I need a comlete another formula to get sum the position?

I hope I can use same approach for the Deliverypositions as well.