Compare sum of hours from two tables and report the differences

[wvuthha]

Hi all,

I have two tables with working hours. One table has the working hours that employees have reported for their work (WORKREPORT). The second table has the real working time per day (ATTENDANCE).

I want to compare the sum per employee and day from the first and second table to show, where there is a difference in the cookings.

This is my first table:

So Tom said, that he worked 5 hours for reporting and 4 hours for administrativ stuff on 9th of Sept. Jane said, she worked 7 hours for reporting on 18th of Sept. Etc.

This is my second table:

The timebookingsystem recorded, that Tom was 8,5 hours in the office on 20th of Sept. Jane was 7 hours in the office on 20th of Sept. Etc.

I now want to create a report that everybody see, if they have reported the right amount of hours.

For example: 

That means, Tom has reported 0,5 hours less than he was in the office on 20th of Sept. On the 21st of Oct, everything is fine, he reported 8 hours what is exactly his hours in the office.

Jane reported correct on 18th and 19th of Sept. But whe forgot the 20th of Sept, where she was in the office but did not report. 

I am not able to solve this, could anybody help me here?

BR
Tom

[wvuthha]

@johnt75 sorry, but now I have another new question around that......

I can now show the difference for every day, for every person. Is it possible in any way to show that aggregated for a month?

[johnt75]

You could do an aggregate like

Aggregated diff =
SUMX ( 'person dimension', [hours diff] )

[wvuthha]

Dear @johnt75 ,

Thank you so much for your help!

cu

Tom

[johnt75 · Accepted solution]

Create a dimension table for the person like

Person dimension =
DISTINCT (
    UNION (
        ALLNOBLANKROW ( Attendance[Name] ),
        ALLNOBLANKROW ( 'Work table'[Name] )
    )
)

and link that to both tables. Create a proper date table, marked as a date table, and link that to both tables as well.

You can then create a measure like

Hours diff =
SUM ( Attendance[Hours] ) - SUM ( 'Work table'[hours] )

Set the filter to only show rows where the value is not 0. This should work at any level of the date hierarchy, e.g. week or month, as well as daily.