Determining activity (in hours) for each employee

[Anonymous]

Hello,

I have a date column indicating the date and time of transaction and an ID column with user IDs. The dates update for the current date and go back seven days. Each row is equivalent to one transaction. What would be the most effective way to determine the number of transactions per hour today and over the past seven days. Basically, I want to make sure that I capture the daily activity time for each user accurately based on time span their transactions are recorded in the system.

Date                                ID

3/9/2021 10:19:51 AM	MDUQUETTE
3/9/2021 10:19:34 AM	VDEMERS
3/9/2021 10:19:22 AM	MDUQUETTE
3/9/2021 10:19:08 AM	MDUQUETTE
3/9/2021 10:18:47 AM	MDUQUETTE
3/9/2021 10:18:29 AM	MDUQUETTE
3/9/2021 10:18:03 AM	MDUQUETTE
3/9/2021 10:17:49 AM	NDAURE
3/9/2021 10:17:43 AM	MDUQUETTE
3/9/2021 10:17:23 AM	MDUQUETTE

[Ashish_Mathur]

Hi,

Split the Date column into Date and Time columns.  Create a Calendar Table and also a Time table.  Build a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  Build a relationship from the Time column of the Data Table to the Time column of the Time Table.  To your visual, drag Date from the Date Table and Time from the Time Table.  Write this measure

=countrows(data)

Hope this helps. 

[Anonymous]

Thank you for your response. I may need to clarify a bit more. I want to visualize the daily number of transactions/ hr for each employee. I am still unable to determine the number of transactions/ hr to measure productivity based on the daily time of activity since not all employees work on the same shift or for the same time span.

[Ashish_Mathur]

Hi,

I am still not clear.  Please show the expected result.

[Anonymous]

Yes, of course. Thank you for your help. The output should be the productivity calculated as -> count of rows/(latest transaction time - first transaction time) for each date and User ID.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.