Count of On Time or Late Orders when some orders lines are on time....and some are late

[Anonymous]

Hello  Community  -   Getting a distinct order count and basing it on if the order was on time or late is fairly straightforward...if you have one order number and one line  (or if multiple lines and all are on time...or all are late). 

But how to write a measure for this scenario where you have some order lines that are on time....and other lines on an order are late?  Our business logic is, if any Order line is late...the Order is defined as late.    And conceivably you could have an Order that appears multiple times, with a different due date than other order lines  (for example if the customer request some lines of the order to ship later, which happens in our business).    Here is a simple table example:  

Order #     Order Line              Due Date       Actual Ship Date

ABC-23         1                              JAN 4               JAN 5

ABC-23         2                              JAN 4               JAN 5

ACD-02        1                               FEB 12             FEB 12

ACD-02        2                               FEB 12             FEB 15 

AGH-09        1                               JAN 17            JAN 17

AGH-09        2                               JAN 17            JAN 21

AGH-09        3                               JAN 25            JAN 25

In the above scenarios, order ABC-23 is On Time.....all lines were on time.      ACD-02 would be considered Late because one of the lines shipped late.    AGH-09 would also be considered Late because one of the lines shipped late.  

So, on a table, I would want to see the following results: 

ABC-23    ON TIME 

ACD-02    LATE  

AGH-09    LATE 

[v-yangliu-msft]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Flag =
var _1=IF('Table'[Actual Ship Dat]>'Table'[Due Date],0,1)
return
SUMX(FILTER('Table','Table'[Order]=EARLIER('Table'[Order])),_1)

2. Create measure.

Result =
var _re=SUMX('Table','Table'[Flag])
return
IF(_re>0,"LATE","ON TIME"
)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[CNENFRNL]

[Anonymous]

Unfortunately this does not give the correct result.   I have a similar measure already that gives these same results.    What that "new status" column should really say is "Late" all the way down.   Because if even one line is late, the whole order needs to be classified as Late.   

[CNENFRNL · Accepted solution]