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 da...
  • 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.

     

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

macpac's avatar
macpac
Regular Visitor
7 years ago

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

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