Create time column based on other column conditions

[Anonymous]

Dear friends 

I need help in order to solve a problem trying to create start counting time column to show the date added 13 hr, in case of the rest time is more than 10 hr indivisually

and event column to show rest time that more than 3 hr but less than 10 hr but date and time must be after start counting time (indivisually too)

could sorted name then asc.order in order to understand what I described better.

Name	Asc.order	Date	Rest	Start counting time	Event
John	7	02-07-20 3:00	5:00		5:00
John	2	01-07-20 2:00	8:00		8:00
John	3	01-07-20 5:00	12:00	01-07-20 18:00
Ron	2	03-07-20 14:00	7:00		7:00
Ron	3	03-07-20 17:00	4:00		4:00
Ron	6	04-07-20 5:00	4:00		4:00
John	4	01-07-20 10:00	3:10
John	5	01-07-20 19:00	7:00		7:00
John	1	01-07-20 1:00	5:00		5:00
Ron	4	03-07-20 20:00	2:00
John	9	02-07-20 11:00	2:00
John	10	02-07-20 16:00	15:00	03-07-20 5:00
John	11	03-07-20 17:00	5:00		5:00
John	8	02-07-20 9:00	11:00	02-07-20 22:00
John	6	01-07-20 20:00	1:00
Ron	1	03-07-20 10:00	12:00	03-07-20 23:00
Ron	5	03-07-20 21:00	1:50

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
You table data is not making it clear
Appreciate your Kudos.

[Anonymous]

@amitchandak if you sort data with name and asc.order it will look like table below

Name	Asc.order	Date	Rest	Start counting time	Event
John	1	01-07-20 1:00	5:00		5:00
John	2	01-07-20 2:00	8:00		8:00
John	3	01-07-20 5:00	12:00	01-07-20 18:00
John	4	01-07-20 10:00	3:10
John	5	01-07-20 19:00	7:00		7:00
John	6	01-07-20 20:00	1:00
John	7	02-07-20 3:00	5:00		5:00
John	8	02-07-20 9:00	11:00	02-07-20 22:00
John	9	02-07-20 11:00	2:00
John	10	02-07-20 16:00	15:00	03-07-20 5:00
John	11	03-07-20 17:00	5:00		5:00
Ron	1	03-07-20 10:00	12:00	03-07-20 23:00
Ron	2	03-07-20 14:00	7:00		7:00
Ron	3	03-07-20 17:00	4:00		4:00
Ron	4	03-07-20 20:00	2:00
Ron	5	03-07-20 21:00	1:50
Ron	6	04-07-20 5:00	4:00		4:00

for start couting time column it will show date column plus 13 hr for rest over 10 hr indivisually

Name	Asc.order	Date	Rest	Start counting time	Event
John	3	01-07-20 5:00	12:00	01-07-20 18:00

for event column, it will display rest that more 3 hr but less than 10 hr for each row indivisually

but its date and time in date column must after lastest start counting time 

Name	Asc.order	Date	Rest	Start counting time	Event
John	3	01-07-20 5:00	12:00	01-07-20 18:00
John	4	01-07-20 10:00	3:10
John	5	01-07-20 19:00	7:00		7:00
John	6	01-07-20 20:00	1:00
John	7	02-07-20 3:00	5:00		5:00

roe 1 rest are more than 10 so start counting time are date column plus 13 hr (01-07-20 5:00 + 13 hr = 01-07-20 18:00)

row 2 rest are more than 3 and less than 10 but date and time is 01-07-20 10:00, its before 01-07-20 18:00 so its event column will blank

row 3,5 rest are more than 3 and less than 10 and date and time are after 01-07-20 18:00 so rest are shown in event column (7:00,5:00)

row 4 rest are less than 3 so event column will blank

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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