ASK HELP ( Getting remark from Lateness and ontime based on multiple logic )

[syaifulefendi]

Hi All

Im looking for help here as i show my table below on Dax formulas

as we can see here . i have table ( A-D ) 
what i expect is ( E) 
based on logic on ( G) 

column F as my note only 

Thanks in advance
SE

[vanessafvg]

can you supply the data in text format rather than a screeenshot?

[syaifulefendi]

SHIFT START	SHIFT END	ACTUAL START	ACTUAL END
15/10/2022 08:30	15/10/2022 16:30	15/10/2022 08:20	15/10/2022 16:20
15/10/2022 08:00	15/10/2022 16:00	15/10/2022 08:20	15/10/2022 16:20
15/10/2022 09:00	15/10/2022 17:00	15/10/2022 09:20	15/10/2022 17:10
15/10/2022 10:00	15/10/2022 18:00	15/10/2022 10:01	15/10/2022 18:00
15/10/2022 15:00	15/10/2022 23:00	15/10/2022 15:06	15/10/2022 23:06
15/10/2022 16:30	16/10/2022 00:30	15/10/2022 16:10	16/10/2022 00:30

[vanessafvg]

I think this does what you need.  But there might be more scenarios necessary to make sure all the rules are captured, see file attached.

[syaifulefendi]

Hi thanks for respond ,

i have some question :
1. what is aftershift ?
2. why actual shift hours same ??
3. where is max present indicator ?

Thanks in advance
SE

[vanessafvg · Accepted solution]

you can change the field labels to your own requirements but if you look at the fields i created in the file (please reference the file you can see the logic for each field i created)  

after 9 shift is a shift thats starts after or on 9 

if(HOUR(ShiftTimes[SHIFT START]) >= 9, 1,0)

please explain the definition of max presence.

[syaifulefendi]

Hi .. sorry 
marked solution clicked . 

Max presence is , tolerable time / flexible shift if employees start more than schedulle . as long as schedulle Not >9am . employees still remark as ontime . and for that , they must exceed when end shift equal the duration they exceeded when start shift 

Thanks 

SE