Forum Discussion

macpac's avatar
macpac
Regular Visitor
7 years ago
Solved

count hours between two datetimes

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's avatar
    v-danhe-msft
    7 years ago

    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's avatar
    macpac
    7 years ago

    Many Thanks Daniel.......

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

     

8 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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's avatar
      macpac
      Regular Visitor
      7 years ago

      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's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        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