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.

     

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

  • macpac's avatar
    macpac
    Regular Visitor
    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.