Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

If Statement with Date and Time Format

Hello, 

 

Need Help with an If statement that includes all of the following factors below. I want to calculate the following

 

If the Driver pick up (Time) is 15 mins <= to the scheduled arrival time than = "On Time" 

Power BI Column:  Driver Pick up Time (1/1/2021 9:30:24 PM ) - Schedule Arrival Time (1/1/2021 9:35:00 PM)

This should pull in "On Time"

 

If the Driver pick up time is 16 mins < than the schedule arrival time than = "Early"

Power BI Column:  Driver Pick up Time (1/1/2021 9:30:24 PM ) - Schedule Arrival Time (1/1/2021 10:35:00 PM)

This Should Pull in "Early" 

 

If the Driver pick up time is > than the scheduled arrival time than  = "Late"

Power BI Column:  Driver Pick up Time (1/1/2021 9:30:24 PM ) - Scheduled Arrival Time (1/1/2021 8:35:00 PM)

This should pull in "Late"

 

My formula for some reason only pulls in "On Time" and nothing else.

Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Anonymous,

     

    Try measure as:

    Measure = 
IF(
    DATEDIFF(MAX('Table'[Driver Pick up Time]),MAX('Table'[Schedule Arrival Time]),MINUTE)<=15 && DATEDIFF(MAX('Table'[Driver Pick up Time]),MAX('Table'[Schedule Arrival Time]),MINUTE)>=0,
    "on time",
    IF(
        DATEDIFF(MAX('Table'[Driver Pick up Time]),MAX('Table'[Schedule Arrival Time]),MINUTE)>=16,
        "Early",
        "Late"
    )
)

    Here is the output:

    The pbix file is attached.

     

    Best Regards,
    Link

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new column like

     

    new column =
    var _diff = datediff([Driver pick up time ],[scheduled arrival time], minute)
    return
    switch(true() ,
    _diff <0 , "Late",
    _diff <=15 , "On Time",
    "Early"
    )

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous,

     

    Try measure as:

    Measure = 
IF(
    DATEDIFF(MAX('Table'[Driver Pick up Time]),MAX('Table'[Schedule Arrival Time]),MINUTE)<=15 && DATEDIFF(MAX('Table'[Driver Pick up Time]),MAX('Table'[Schedule Arrival Time]),MINUTE)>=0,
    "on time",
    IF(
        DATEDIFF(MAX('Table'[Driver Pick up Time]),MAX('Table'[Schedule Arrival Time]),MINUTE)>=16,
        "Early",
        "Late"
    )
)

    Here is the output:

    The pbix file is attached.

     

    Best Regards,
    Link

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This is perfect thank you!!!