Calculated Table for Work Hours

[Anonymous]

Hi everyone,

I am new to DAX and have been playing around with this problem for a few days now. I have two tables with the same dimensionality of ~4000 rows (employees) by ~200 columns (dates). One records the time-ins of employees and the other records the time-outs in Date/Time format. My objective is to create a new calculated table with exactly the same dimensions (employees x dates) with the number of work hours the employees worked each day. I realize that calculating the work hours would involve using the DATEDIFF function but I would have to do it for all the column pairs (e.g. DATEDIFF(time_out[2015-01-01], time_in[2015-01-01], HOUR), DATEDIFF(time_out[2015-01-02], time_in[2015-01-02], HOUR), ...) --200 times!

How do I create an expression for the calculated table to apply the same DATEDIFF function on the corresponding cells of time-in and time-out tables and put the result in the work-hours table?

-Thanks!!

[HotChilli · Accepted solution]

The straight answer is "you don't".

I think you could "unpivot other columns"  (in Power Query) from the employee id in each table and get yourself some columnar data like

emp_id date time_in

You could probably 'Merge as New' and join both tables on date and employee id.

That'll give you a table with emp_id date time_in time_out and you can use your DATEDIFF function to add another column.

[Anonymous]

Thanks so much HotChilli! That makes a lot of sense!!