Using IF to see if an appointment was early or late

[android1]

Hi,

I am using the following calculated column which tells me if an appointment was early or late -> Punctuality = IF (OTIF[DutyTimeFrom]<OTIF[TimeFrom]-1/24*.25,"Early",IF (OTIF[DutyTimeFrom]>OTIF[TimeFrom]+1/24*.25,"Late"

An appointment is early if it is 15mins before start time so if appointment time is 17:00 then you are early if you arrive before 16:45.

Appointment is late if it is 15 mins after start time so you are late if you arrive after 17:15.

I now need to only show early and late if you are at most 15 mins early or late. In the example above, early would be before 16:45 but not before 16:30 ie between 16:45 and 16:30. Late would be between 17:15 and 17:30.

[SamLester · Accepted solution]

You can use the AND function to enforce the two constraints.

Ex: 

IF (AND(OTIF[DutyTimeFrom]<otif[timefrom]-1/24*.25, OTIF[DutyTimeFrom] > otif[timeFrom] - 1/24 * .5),"early", "not early enough")

http://social.technet.microsoft.com/wiki/contents/articles/682.power-bi-dax-logical-functions.aspx

AND Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise returns FALSE.

Returns true or false depending on the combination of values that you test.

Syntax

AND(<Logical1>, <logical2>)

Thanks,
Sam Lester (MSFT)

[KHorseman · Accepted solution]

You can string together longer AND statements using the && operator. The AND function can only check two conditions unless you nest sub-ANDs.

IF (

OTIF[DutyTimeFrom]<otif[timefrom]-1/24*.25 &&

OTIF[DutyTimeFrom] > otif[timeFrom] - 1/24 * .5,

"early",

"not early enough")

...will behave the same way, and if you have some third condition you can just tack on another && <insert condition here>.

[SamLester · Accepted solution]

You can use the AND function to enforce the two constraints.

Ex: 

IF (AND(OTIF[DutyTimeFrom]<otif[timefrom]-1/24*.25, OTIF[DutyTimeFrom] > otif[timeFrom] - 1/24 * .5),"early", "not early enough")

http://social.technet.microsoft.com/wiki/contents/articles/682.power-bi-dax-logical-functions.aspx

AND Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise returns FALSE.

Returns true or false depending on the combination of values that you test.

Syntax

AND(<Logical1>, <logical2>)

Thanks,
Sam Lester (MSFT)

[KHorseman · Accepted solution]

You can string together longer AND statements using the && operator. The AND function can only check two conditions unless you nest sub-ANDs.

IF (

OTIF[DutyTimeFrom]<otif[timefrom]-1/24*.25 &&

OTIF[DutyTimeFrom] > otif[timeFrom] - 1/24 * .5,

"early",

"not early enough")

...will behave the same way, and if you have some third condition you can just tack on another && <insert condition here>.

[Sean · Accepted solution]

@android1 This should do it

Punctuality =
SWITCH (
    TRUE (),
    OTIF[DutyTimeFrom] < OTIF[TimeFrom] - 1/24*0.25 && OTIF[DutyTimeFrom] >= OTIF[TimeFrom] - 1/24*0.5, "Early",
    OTIF[DutyTimeFrom] > OTIF[TimeFrom] + 1/24*0.25 && OTIF[DutyTimeFrom] <= OTIF[TimeFrom] + 1/24*0.5, "Late",
    "Don't Care!"
)

[android1]

Thanks everyone. All these work perfectly.