How to workout the Variance of Hours Captured & Available Hours?

[rush]

I have 2 tables called V_TIMESHEET & Avail Hours

V_TIMESHEET contains the following columns:

• StaffID (Numeric)
• Year-Month (Text)
• HoursCaptured (Numeric)

Avail Hours contains the following columns:

• Year-Month (Text)
• Available Hours (Numeric)

Avail Hours contains unqiue values of the Year-Month (16-03) in text while V_TIMESHEET has duplicates of Year-Month which they are joined on.

I am needing to work out the Sum of the HoursCaptured per StaffID per Year-Month then Subtract that from the Available Hours that was available for that Year-Month

[Eric_Zhang · Accepted solution]

---

@rush wrote:

I have 2 tables called V_TIMESHEET & Avail Hours

V_TIMESHEET contains the following columns:

• StaffID (Numeric)
• Year-Month (Text)
• HoursCaptured (Numeric)

Avail Hours contains the following columns:

• Year-Month (Text)
• Available Hours (Numeric)

Avail Hours contains unqiue values of the Year-Month (16-03) in text while V_TIMESHEET has duplicates of Year-Month which they are joined on.

I am needing to work out the Sum of the HoursCaptured per StaffID per Year-Month then Subtract that from the Available Hours that was available for that Year-Month

---

@rush

You can try to create proper relationship between those two tables and create a measure as below. Check more details in the attached zip file.

Measure = SUM(V_TIMESHEET[HoursCaptured])-SUM('Avail Hours'[Avaiable Hours])

[rush]

@Eric_Zhang Thanks a lot.

It works well.