Forum Discussion

Ashik008's avatar
Ashik008
Frequent Visitor
1 year ago
Solved

Time in and time out

Hi all,need help

 

i have data like this 

 

empidid swap time
A12/24/2024:10:00am
A12/24/2024:13:00pm
A12/24/2024"13:00pm
A12/24/2024"13:10pm
A12/24/2024"19:10pm
A12/24/2024"19:10pm
A12/23/2024:9:00am
A12/23/2024:20:00pm
B12/24/2024:10:00am
B12/24/2024:13:00pm
B12/24/2024"13:00pm
B12/24/2024"13:10pm
B12/24/2024"19:10pm
B12/24/2024"19:10pm
B12/23/2024:10:00am
B12/23/2024:21:00pm

 

im looking for a result like this 

empidDateTime 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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • ChainsawMan's avatar
    ChainsawMan
    Icon for Helper I rankHelper I
    1 year ago

    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])
)
  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    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

    A12/23/202409:00:00 AM08:00:00 PM
    A12/24/202410:00:00 AM07:10:00 PM
    B12/23/202410:00:00 AM09:00:00 PM
    B12/24/202410:00:00 AM07:10:00 PM

    Let me know if you encounter any issues! 😊

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.