Get users' check-in and check-out time

[jorge_mu]

Hello

I have a table in excel that records the input and output of users.

A user can enter and exit once or many times during the day.

As I can show: User, date, first time of entry and last departure time; as shown in the attached image.

Thank you

Jorge.

[amitchandak]

@jorge_mu , Create a nee table like. Based on Initial Screenshot. Change the value as per need

Summarize(Table,[USUSURIO],[FECHA], "ENTRAR" ,Minx([ESTADO] = "ENTRAR",[HORA] ), "SUAR" ,Maxx([ESTADO] = "SUAR",[HORA] ) )

Or column in the Same Table

Minx(filter(Table, [USUSURIO] =earlier([USUSURIO]) && [FECHA] earlier([FECHA]) && [ESTADO] = "ESTADO"),[HORA])
Minx(filter(Table, [USUSURIO] =earlier([USUSURIO]) && [FECHA] earlier([FECHA]) && [ESTADO] = "SUAR"),[HORA])

[Ashish_Mathur]

Hi,

Share the tables in a format that can be pasted in MS Excel.  Also, could you fill the cells in English language?

[jorge_mu]

DAIRY RECORD

DATE	HOUR	ESTATUS	USER
9/6/20	19:00	LOGOUT	LUIS
9/6/20	07:59	LOGIN	LUIS
9/6/20	15:45	LOGOUT	CARMEN
9/6/20	10:00	LOGIN	CARMEN
9/6/20	17:00	LOGOUT	JOSE
9/6/20	13:30	LOGIN	JOSE
9/6/20	12:00	LOGOUT	JOSE
9/6/20	08:01	LOGIN	JOSE
9/5/20	17:50	LOGOUT	LUIS
9/5/20	14:00	LOGIN	LUIS
9/5/20	13:00	LOGOUT	LUIS
9/5/20	10:10	LOGIN	LUIS
9/5/20	09:40	LOGOUT	LUIS
9/5/20	07:50	LOGIN	LUIS
9/5/20	18:01	LOGOUT	JOSE
9/5/20	02:30	LOGIN	JOSE
9/5/20	11:30	LOGOUT	JOSE
9/5/20	07:20	LOGIN	JOSE
9/5/20	17:00	LOGOUT	CARMEN
9/5/20	07:30	LOGIN	CARMEN

SUMMARY 

USER	DATE	LOGIN	LOGOUT
LUIS	9/6/20	07:59	19:00
CARMEN	9/6/20	10:00	15:45
JOSE	9/6/20	08:01	17:00
LUIS	9/5/20	07:50	17:50
JOSE	9/5/20	07:20	18:01
CARMEN	9/5/20	07:30	17:00

[Ashish_Mathur · Accepted solution]

Hello

You can download my PBI file from here.

I hope this helps.

[jorge_mu]

Hello

The measure Logout is like this:

Logout = CALCULATE(MAX... because i need to show the max logout time.

Thank u

Best regards,

Jorge

[Anonymous]

HI @jorge_mu 

Use the same data in matrix chart and use Min and Max for the time adding the "Hora" column twoce in the chart.

When you add the "Hora" column for the first tine there is a little symbol next to the Hora, click that and select Min.

Now add he "Hora" column again in the chart and select max from the same little symbol.