Forum Discussion

RahulPBI's avatar
RahulPBI
Icon for Helper I rankHelper I
6 years ago
Solved

Calculating If Delivery is Early, On Time or Late based on two dates

Hello

I am new to power BI and looking for some help with Power Query M language

 

I have a formula that works great in Excel and am looking for some help in recreating it in power query

=IF([@[Lead Time]]="","On Time", IF([@[Lead Time]]=[@[Actual Ship Date]],"On Time",IF([@[Lead Time]]>[@[Actual Ship Date]],"Early","Late")))

 

What i am trying to do is calculate deliveries

 

Can someone please help me

 

Regards

RBI

Need Help
  • artemus's avatar
    artemus
    6 years ago

    In Add column this would be:

    = if [Lead Time] = "" or [Lead Time] = [Actual Ship Date] then 
   "On Time"
else if [Lead Time] >= [Actual Ship Date] then
   "Early"
else
   "Late"

3 Replies

  • artemus's avatar
    artemus
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    In Add column this would be:

    = if [Lead Time] = "" or [Lead Time] = [Actual Ship Date] then 
   "On Time"
else if [Lead Time] >= [Actual Ship Date] then
   "Early"
else
   "Late"
    • RahulPBI's avatar
      RahulPBI
      Icon for Helper I rankHelper I
      6 years ago

      Hi Artemus

      Thanks for your reply

      I treid your solution, but when the Lead Time is "" or Null it shows Late instead of On Time

       

      Thanks

       

       

    • RahulPBI's avatar
      RahulPBI
      Icon for Helper I rankHelper I
      6 years ago

      Hi Artemus

      Worked it out, instead of a custom column i used a Conditional Column and have got what I needed

       

       

      Thanks for your guidance

       

      Regard