Help with DAX for missing hours

[AndyDD_UK]

In table A I have number hours entered by employee, day and activity. Example of table:

Employee_id / DateofActivity / Activity (project id) / Hours Spent on Activity / Datetime entered onto timesheet system

In table B I have hours expected to work by week by employee_id, example of table:

Employee_id / Standard_hours

123                  40

In my Cube I have a measure called 'missinghours_overtime' which is = sum(TableA[HoursSpentActivity]) - sum(TableB[Standard_hours])

In Power BI report I can show missinghours_overtime' by employee by week and as a total.

Example:

Employee123 shows 10 hours overtime in week 1 and 20 hours missing for week 2, making a total of 10 missing hours.

How can I only focus on missing hours, ie have the report show 0 missing hours in week 1 and 20 missing hours in week 2 making a total of 20 missing hours ?

[Ashish_Mathur]

Hi,

Share some data to work with (in a format the can be pasted in an MS Excel file) and show the expected result.

[AndyDD_UK]

Thanks @Ashish_Mathur , does this work for you ? https://docs.google.com/spreadsheets/d/1Umr0xfRTZQxQ0M4L1cYDDJ4zj4V5-RRfgAoPrDkfIHk/edit?usp=sharing

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.