Forum Discussion
Calculated Table for Work Hours
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!!
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.
2 Replies
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.