Comparing data on two dates from different tables

[Richard77]

Dear community,

I’m looking for some help because I am unable to grasp what I am doing wrong. I am not a Power BI wizard so it might be something easy 😉

I want a visual that shows the number of contract hours per month and the number of hours registered and then an “easy” calculation of the % column.

The struggle is with combining the 2 tables in order to create the table-visual.

I have 2 tables:

TT_EMP_CONTRACT

This table has no index. Each row represents a contracting period using the EMP_ID, start_date, end_date and the amount of hours.

TT_HRS

This table has an index and each row represents an activity done. For each row the ACT_ID, EMP_ID, DATE and the amount of hours is registered.

In TT_EMP_CONTRACT I added a date which allowed me to create rows for each day showing the number of contracted hours per day (or rather, the contracted hours are registered in a weekly number, not in a daily, but dividing this by 5 gives me an accurate enough number).

After trying several merges or date table tricks I am still unable to create the overview I want. In most cases I miss certain rows from TT_EMP_CONTRACT after merging it (Left Outer) with TT_HRS.

(next to this I am using 2 tables for filtering purposes: TT_ACT including the activity meta data and TT_EMP including the employee data)

Any help is greatly appreciated!

[Richard77 · Accepted solution]

Hi all,

for now i solved it after creating a date table (Excellent video from Russo: https://www.youtube.com/watch?v=FxiAYGbCfAQ&t=1309s)

Thanks!