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.