Time in and time out

[Ashik008]

Hi all,need help

i have data like this 

empid	id swap time
A	12/24/2024:10:00am
A	12/24/2024:13:00pm
A	12/24/2024"13:00pm
A	12/24/2024"13:10pm
A	12/24/2024"19:10pm
A	12/24/2024"19:10pm
A	12/23/2024:9:00am
A	12/23/2024:20:00pm
B	12/24/2024:10:00am
B	12/24/2024:13:00pm
B	12/24/2024"13:00pm
B	12/24/2024"13:10pm
B	12/24/2024"19:10pm
B	12/24/2024"19:10pm
B	12/23/2024:10:00am
B	12/23/2024:21:00pm

im looking for a result like this 

empid	Date	Time in(First id swap time)	Time out(Last id swap time)
A
A
A
A
A
A
A
A
B
B
B
B
B
B
B
B

please help

[Anonymous · Accepted solution]

Hi @Ashik008 

Please try this:

Click the Transform data in the Home pane:

Select the id swap time column and split the column by positions

Remove the id swap time.2 column:

Rename the id swap time.1 as Date.

Click the Group by in the Transform pane:

The result is as follow:

Or you can try to use this dax formula to create a calculated table, but it's noting that there you should make sure that the type of the [id swap time] is date/time:

Table 2 = 
	VAR _vtable = ADDCOLUMNS(
		'Table',
		"_Date", DATE(YEAR('Table'[id swap time]), MONTH('Table'[id swap time]), DAY('Table'[id swap time])),
		"_Time", TIME(
			HOUR('Table'[id swap time]),
			MINUTE('Table'[id swap time]),
			SECOND('Table'[id swap time])
		)
	)
	RETURN
		SUMMARIZE(
			SELECTCOLUMNS(
				_vtable,
				'Table'[empid],
				[_Date],
				"Time in", MINX(
					FILTER(
						_vtable,
						'Table'[empid] = EARLIER('Table'[empid]) && [_Date] = EARLIER([_Date])
					),
					[_Time]
				),
				"Time out", MAXX(
					FILTER(
						_vtable,
						'Table'[empid] = EARLIER('Table'[empid]) && [_Date] = EARLIER([_Date])
					),
					[_Time]
				)
			),
			[empid],
			[_Date],
			[Time in],
			[Time out]
		)

The result is as follow:

Best Regards

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

[FarhanJeelani]

Hi @Ashik008,

To achieve your desired result in Power BI, follow these steps:

1. Load the Data

• Import your data into Power BI.
• Ensure the swap time column is correctly recognized as a Date/Time type.

2. Create a New Column for Date

Add a calculated column to extract the date from the swap time field:

1. Go to the "Modeling" tab and click "New Column."
2. Use the following DAX formula:

   Date = DATE(YEAR([swap time]), MONTH([swap time]), DAY([swap time]))

3. Create Time In (First Swap Time)

Add a calculated column to find the earliest swap time for each empid and Date:

1. Click "New Column."
2. Use the following DAX formula:

   Time In = 
   CALCULATE(
       MIN('YourTableName'[swap time]),
       ALLEXCEPT('YourTableName', 'YourTableName'[empid], 'YourTableName'[Date])
   )

4. Create Time Out (Last Swap Time)

Add a calculated column to find the latest swap time for each empid and Date:

1. Click "New Column."
2. Use the following DAX formula:

   Time Out = 
   CALCULATE(
       MAX('YourTableName'[swap time]),
       ALLEXCEPT('YourTableName', 'YourTableName'[empid], 'YourTableName'[Date])
   )

5. Create a Summary Table

If you want a cleaner, summarized view with one row per empid per Date, create a summarized table:

1. Go to the "Modeling" tab and click "New Table."
2. Use this DAX formula:

   SummaryTable = 
   SUMMARIZE(
       'YourTableName',
       'YourTableName'[empid],
       'YourTableName'[Date],
       "Time In", MIN('YourTableName'[swap time]),
       "Time Out", MAX('YourTableName'[swap time])
   )

6. Visualize the Results

• Use a table or matrix visual in Power BI.
• Add the fields: empid, Date, Time In, and Time Out.

Result in Power BI:

empid Date Time In Time Out

A	12/23/2024	09:00:00 AM	08:00:00 PM
A	12/24/2024	10:00:00 AM	07:10:00 PM
B	12/23/2024	10:00:00 AM	09:00:00 PM
B	12/24/2024	10:00:00 AM	07:10:00 PM

Let me know if you encounter any issues! 😊

Please mark this as solution if it helps you. Appreciate Kudos.

[ChainsawMan]

Hi,

You could create a calculated table like this:

Result_Table =
SUMMARIZE(
    'YourDataTable',                      
    'YourDataTable'[empid],               
    'YourDataTable'[swap time],          
    "Time In", MIN('YourDataTable'[swap time]), 
    "Time Out", MAX('YourDataTable'[swap time]) 
)

Or you could create two measures and show them in any visuals:

Time_In =
CALCULATE(
    MIN('YourDataTable'[swap time]),
    ALLEXCEPT('YourDataTable', 'YourDataTable'[empid])
)

Time_Out =
CALCULATE(
    MAX('YourDataTable'[swap time]),
    ALLEXCEPT('YourDataTable', 'YourDataTable'[empid])
)