Forum Discussion

SN1900's avatar
SN1900
Frequent Visitor
7 years ago
Solved

IF formular

Hello all, 

 

I have an issue with an IF formular as I do not now how i can build the if formular in Power BI as I am quite new in Power BI.

 

I have two  different columns from two data fields with a date. Now I want to compare the dates. 

My Idea: IF both dates match than it would be "on Time".

Otherwise it should be saying : too late or too early depends on the date.

In Excel my formular would be:

IF(Delivery Appointment DATE=Event Date NEW UTC ;"On Time";IF(Delivery Appointment DATE<Event Date NEW UTC;"Early";"Late")) 

Thanks and best regards 

 

  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi SN1900 

     

    Please see the below.

    Column = 
VAR x = (
    CALCULATE(
        MAX( Event[Event Date NEW UTC] ), 
        Event[STATUS] = "DELIVERED" 
    ) - Delivery[Delivery Appointment DATE] 
) * 1
RETURN 
SWITCH(
    TRUE,
    x = 0, "On Time",
    x > 0, "Late",
    "Early"
)
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski



6 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi SN1900 

     

    Please see the below column expression.

    Column = 
SWITCH(
    TRUE(),
    'Table'[Delivery Appointment DATE] = 'Table'[Event Date NEW UTC], "On Time",
    'Table'[Delivery Appointment DATE] > 'Table'[Event Date NEW UTC], "Late",
    "Early"
)

    Result.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • SN1900's avatar
      SN1900
      Frequent Visitor
      7 years ago

      Hi Mariusz, 

       

      thanks for your fast  response!

       

      I tried your formular by adding the new column with the formular in the dataset shipment Analysis. 

      Therefore I could only select the "Delivery Appointment" data as dropdown and I had too enter in the "Event data new utc" manually as this data comes from another field. 

       

      Now I reiceved this error message saying:A single value for column "event data new utc" in table "otm event status cannot" be determined. 

      Just for your information.

      It is possible that there are more event dates but only one delivery appointment date. could that be the issue?

       

       

       Many thanks and best regards 

       

      Niclas 

       

       

       

       

       

       

       

       

       

       

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi SN1900 

         

        Sorry the solution I gave you works on the assumption that the data was in one table, what is the relationship between this tables.

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski


         

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Why are you doing this in DAX instead of in Power Query, where this calculation truly belongs?

    Best
    Darek