count hours between two datetimes

[macpac]

I have a date query with every hour of every day (ie. 11/30/2017 10:00PM, 11/30/2017 11:00PM, 12/1/2017 12:00AM, 12/1/2017 1:00AM, etc.....) and a query with employee numbers and their clock punch data (ie. empl# 12345, InPunch 11/30/2017 10:00PM, OutPunch 12/1/2017 1:00AM).

I want to count how many employees were clocked in at each hour of each day.  I have tried to do an IF-THEN and put the count in a new column in the date query, but the InPunch and OutPunch columns are not accessible with IF-THEN.

Has anyone else dealt with a similar issue that can point me in the right direction?

Thanks,

macpac

[v-danhe-msft · Accepted solution]

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

[v-danhe-msft]

Hi @macpac,

Based on my test, you could refer to below steps:

Sample data:

Create two calculated columns in Table1.

a = CALCULATE (
            VALUES(Table2[empl number]),
            FILTER (
                ALL ( 'Table2' ),
                'Table2'[InPunch] <= EARLIER(  'Table1'[Date times] )
                    && 'Table2'[OutPunch] >= EARLIER(  ( 'Table1'[Date times] ) )
            )
        )

Average number of each hour = DIVIDE(AVERAGE(Table1[a]), 
                                     DATEDIFF(CALCULATE(MIN('Table1'[Date times]),FILTER('Table1','Table1'[a]=EARLIER(Table1[a]))),
                                              CALCULATE(MAX('Table1'[Date times]),FILTER('Table1','Table1'[a]=EARLIER(Table1[a]))),HOUR)
                                              )

Result:

You could also download the pbix file to have a view:

https://www.dropbox.com/s/6pbyllagrgq4n9i/count%20hours%20between%20two%20datetimes.pbix?dl=0

Regards,

Daniel He

[macpac]

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

[v-danhe-msft]

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

[macpac]

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

[v-danhe-msft]

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

[macpac]

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!

[v-danhe-msft · Accepted solution]

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

[macpac · Accepted solution]

Many Thanks Daniel.......

This resolved my challenge and I was able to apply this logic to a transaction file with a similar issue.