Forum Discussion

Deeks's avatar
Deeks
Frequent Visitor
3 years ago

Please help with calculated column (If statements)

Hi, posting this again as I don’t think my previous post was clear.  

 

I’m trying to create a column to determine if an order has been shipped on time. An order is considered to be shipped on time if it has been marked as shipped and its current ship date matches (or is within 3 days) of the original ship date, if it is over 3 days then it is considered late. I have managed to come up with a formula for this - “IfOnTime2”. However, this was penalising the order multiple times, for every current ship date change, and I need it to be only penalised once, to correct this, I have created another column “IfOnTime”.

 

Now, I have realised that there are some orders which may have been shipped earlier, but my formula is penalising this too. Could anyone please help correct this? So, if an order has a current ship date which is before the original ship date, it is marked as early, rather than on time or late.

 

Could someone also please help me with how I’d go about optimising my code for my original two “IfOnTime” columns?

 

Thank you – data set posted below.

 

ShipmentData 

 

"Calculated Column"
"dax code"
"Need Help"

2 Replies

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    pelase try 

    on time?column =
    SWITCH(
        TRUE(),
        AND(OOR[isShipped]=TRUE(),OOR[Original Ship Date] <= OOR[Current Ship Date] + 3),"Yes","No")



    if i didnt understand your question , can you please be more specific on what your goal is and waht column we should look at?


    • Deeks's avatar
      Deeks
      Frequent Visitor
      3 years ago

      Hi, not quite but your code gave me a hint as to how I could calculate what was needed, and have now managed to solve it using switch statements. Thank you! 🙂