Forum Discussion
count hours between two datetimes
Hi macpac,
From your description, I have modified my pbix, you could refer to below steps:
1.Pivot the JobDescription column.
2.Apply it and create three calculated columns in Table2.
Count of Cashier = CALCULATE ( COUNT(Table1[Cashier]), FILTER ( ALL ( 'Table1' ), 'Table1'[Start of In_Hour] <= EARLIER( Table2[DateTime] ) && 'Table1'[Start of Out_Hour] >= EARLIER( ( 'Table2'[DateTime] ) ) ) )Count of Cook = CALCULATE ( COUNT(Table1[Cook]), FILTER ( ALL ( 'Table1' ), 'Table1'[Start of In_Hour] <= EARLIER( Table2[DateTime] ) && 'Table1'[Start of Out_Hour] >= EARLIER( ( 'Table2'[DateTime] ) ) ) )Count of Groundskeeper = CALCULATE ( COUNT(Table1[Groundskeeper]), FILTER ( ALL ( 'Table1' ), 'Table1'[Start of In_Hour] <= EARLIER( Table2[DateTime] ) && 'Table1'[Start of Out_Hour] >= EARLIER( ( 'Table2'[DateTime] ) ) ) )Now you could see the result:
You could also download the pbix to have a view:
https://www.dropbox.com/s/464gt6bpy7ggarm/count%20hours%20between%20two%20datetimes3.pbix?dl=0
Regards,
Daniel He
Many Thanks Daniel.......
This resolved my challenge and I was able to apply this logic to a transaction file with a similar issue.
Thank you Daniel,
However, I get the message "A table of multiple values was supplied where a single value was expected.". Most likely, because there are multiple entrys for employee ID 12345 since they work almost everyday and I need to do this calculation for at least one month of business days.
Any other ideas how to deal with multiple employees working on various days and times in my employee timepunch table? Of course, the Date table has a unique row for each day/hour.
I did try to add the columns to my employee timepunch table instead of the Date table, but I got the message "A single value for column 'DateTime' in table 'D_T' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."; which I did not expect since the appears to be unique in the DateTime column (ie. 11/30/2017 10:00PM, 11/30/2017 11:00PM, 12/1/2017 12:00AM, 12/1/2017 1:00AM, etc.....).
I would like to data to appear as such......
DateTime count of empl this hour
11/30/2017 10:00PM 3
11/30/2017 11:00PM 3
12/1/2017 12:00AM 2
12/1/2017 1:00AM 4
12/1/2017 2:00AM 4
12/1/2017 3:00AM 5
etc........
12/31/2017 11:00PM 2
Hi macpac,
Due to I may misunderstand what you want to get, could you please offer me some sample data in another table that contains the [empl] column?
Regards,
Daniel He
I would like my final output to look something like this......
Job Description DateTime Count of Employees
Cashier 11/30/2017 7:00PM 1
Cashier 11/30/2017 8:00PM 1
Groundskeeper 11/30/2017 8:00PM 1
Cashier 11/30/2017 9:00PM 1
Groundskeeper 11/30/2017 9:00PM 1
Cashier 11/30/2017 10:00PM 5
Groundskeeper 11/30/2017 10:00PM 1
Assistant Store Manager 11/30/2017 10:00PM 1
Cashier 11/30/2017 11:00PM 4
etc.......................
Employee table -- EmployeeKey data type is whole number; ...In_Hour and ...Out_Hour data types are Date/Time
JobDescription
InPunchHour
OutPunchHour
EmployeeKey
Start of In_Hour
Start of Out_Hour
Cashier
19
22
193147
11/30/2017 7:00:00 PM
11/30/2017 10:00:00 PM
Groundskeeper
20
23
187901
11/30/2017 8:00:00 PM
11/30/2017 11:00:00 PM
Cashier
22
2
210097
11/30/2017 10:00:00 PM
12/1/2017 2:00:00 AM
Cook
22
3
191476
11/30/2017 10:00:00 PM
12/1/2017 3:00:00 AM
Cashier
22
3
206925
11/30/2017 10:00:00 PM
12/1/2017 3:00:00 AM
Assistant Store Manager
22
3
209439
11/30/2017 10:00:00 PM
12/1/2017 3:00:00 AM
Cashier
22
2
189702
11/30/2017 10:00:00 PM
12/1/2017 2:00:00 AM
Cashier
22
4
209206
11/30/2017 10:00:00 PM
12/1/2017 4:00:00 AM
Assistant Store Manager
3
7
209439
12/1/2017 3:00:00 AM
12/1/2017 7:00:00 AM
Cashier
3
7
210097
12/1/2017 3:00:00 AM
12/1/2017 7:00:00 AM
Date table -- DateTime data type is Date/Time
DateTime
11/30/2017 5:00:00 PM
11/30/2017 6:00:00 PM
11/30/2017 7:00:00 PM
11/30/2017 8:00:00 PM
11/30/2017 9:00:00 PM
11/30/2017 10:00:00 PM
11/30/2017 11:00:00 PM
12/1/2017 12:00:00 AM
12/1/2017 1:00:00 AM
12/1/2017 2:00:00 AM
12/1/2017 3:00:00 AM
12/1/2017 4:00:00 AM
12/1/2017 5:00:00 AM
12/1/2017 6:00:00 AM
12/1/2017 7:00:00 AM
12/1/2017 9:00:00 AM
12/1/2017 10:00:00 AM
12/1/2017 11:00:00 AM
Hi macpac,
Based on my test, you could refer to below steps:
Sample data:
Create a calculated column in Table2:
Count of employee = CALCULATE ( COUNT(Table1[EmployeeKey]), FILTER ( ALL ( 'Table1' ), 'Table1'[Start of In_Hour] <= EARLIER( Table2[DateTime] ) && 'Table1'[Start of Out_Hour] >= EARLIER( ( 'Table2'[DateTime] ) ) ) )Create a table visual and add the related field, now you can see the result:
You could also download the pbix file to have a view:
https://www.dropbox.com/s/3uvt9z9jebaekwq/count%20hours%20between%20two%20datetimes2.pbix?dl=0
Regards,
Daniel He
How can I go one more step and count the employees by "Job Description" by hour?
Your solution gave me the total employees by hour, but now I'm being asked to break the total out by "Job Description".
For example, per my sample data I would have the following information.....
DateTime Cashier GroundsKeeper Asst. Manager Cook
11/30/2017 10:00PM 5 1 1 1
.... OR .....
DateTime Cashier GroundsKeeper Asst. Manager Cook
11/30/2017 10:00PM 5
11/30/2017 10:00PM 1
11/30/2017 10:00PM 1
11/30/2017 10:00PM 1
Thank you for all you have done!