in out time calculation

[rajasekar_o]

hi team , i need to calculate in out time 
in time is max of in time on that date 
out time is if the out time available then max of outtime,if ther is no out time then check next day if there is a out time before 8 am then take it as out time of that date .
i give the data 

ID	DATETIME	DATE	TIME	TYPE
RAM	29.1.2023.9:30:00	29.1.2023	09:30:00	IN
RAM	29.1.2023.9:40:00	29.1.2023	09:40:00	IN
KUMAR	29.1.2023.9:30:00	29.1.2023	09:30:00	IN
RAM	29.1.2023.16:30:00	29.1.2023	16:30:00	OUT
KUMAR	29.1.2023.17:30:00	29.1.2023	17:30:00	OUT
RAM	30.1.2023.9:30:00	30.1.2023	09:30:00	IN
KUMAR	30.1.2023.10:30:00	30.1.2023	10:30:00	IN
RAM	31.1.2023.3:30:00	31.1.2023	03:30:00	OUT
RAM	31.1.2023.3:30:19	31.1.2023	03:30:19	OUT

expected output

[Anonymous · Accepted solution]

Hi @rajasekar_o ,

Please follow these steps:

1.Creating intime measure.

intime =

VAR time = MAXX(FILTER(ALL('Table'),'Table'[ID] = MAX('Table'[ID]) && 'Table'[DATE ] = MAX('Table'[DATE ]) && 'Table'[TYPE] = "IN"),'Table'[TIME ])

RETURN

FORMAT(time,"hh:mm:ss")

2.Creating outtime measure.

outtime =

var cur_name=SELECTEDVALUE('Table'[ID])

var cur_dt=SELECTEDVALUE('Table'[DATE ])

var cur_date=DATE(YEAR(cur_dt),MONTH(cur_dt),DAY(cur_dt))

var next_date=cur_date+1

var tmp=FILTER(ALL('Table'),DATE(YEAR([DATE ]),MONTH([DATE ]),DAY([DATE ]))=cur_date&&[TYPE]="OUT"&&'Table'[ID] = cur_name)

var max_date=MAXX(tmp,[DATE ])

VAR time = MAXX(tmp,[TIME ])

var max_time=TIME(HOUR(time),MINUTE(time),SECOND(time))

var tmp2=FILTER(ALL('Table'),[ID]=cur_name&&DATE(YEAR([DATE ]),MONTH([DATE ]),DAY([DATE ]))=next_date&&[Type]="OUT")

var tmp3=FILTER(tmp2,[TIME ]<TIMEVALUE("8.00.00"))

var max_time1=MAXX(tmp3,[TIME ])

RETURN

IF(ISBLANK(COUNTROWS(tmp)),

FORMAT(max_time1,"hh:mm:ss"),

FORMAT(max_time,"hh:mm:ss")

)

3.Create another measure for filtering dates with only OUT.

Measure =

var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[DATE ]=MAX('Table'[DATE ])),"Type1",[TYPE])

return

IF(

    "IN" in _select,1,0)

The settings inside filters are shown below.

4.The result obtained is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[amitchandak]

@rajasekar_o ,

Measure like

intime=

minx(filter(Table, Table[Type] = "IN"), Table[intime])

outtime =

maxx(filter(Table, Table[Type] = "OUT"), Table[intime])