Forum Discussion
Helper Vin out time calculation
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
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.
2 Replies
- amitchandak
Super User
Measure like
intime=
minx(filter(Table, Table[Type] = "IN"), Table[intime])
outtime =
maxx(filter(Table, Table[Type] = "OUT"), Table[intime])
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.
