If Statement with Date and Time Format

[Anonymous]

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.

[v-xulin-mstf · Accepted solution]

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]

This is perfect thank you!!!

[amitchandak]

@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"
)