Cumulative working hours vs daily targets across all dates

[hemalpatel]

Hello,

I'm struggling getting the correct totals for a time recording/ tracking report. Also, early apology for the massive post - I just wanted to include as much info as possible.

Right, I have a date dimension table and a fact table with the number of hours worked like below:

Each employee has a daily target of 7 hours. Therefore, even though this might be unecessary, i've included a column of ExpectedHours in the DateDimension table.

I need to be able to show how much time is missing per day, per employee and be able to roll that up for the week/ month/ year etc. I created a measure to sum the hours but the issue i'm getting is when an employee hasn't recorded any time at all for the day. 

I partly got this working with IF(ISNULL for both adding a 0 to the HoursRecorded sum and a 7 to the ExpectedHours sum for where records do not exist. Example below (red line is where the data was missing/ green highlight is where it's fixed):

However, i'm getting an issue with the Totals for all of the sums that have the ISNULL check. It seems that, whilst the nulls are filled with the relavent 0' and 7's, these aren't included in the total calculations. Example for one employee below:

In the example above (left table), you can see that the totals for ExpectedHours and HoursMissing (the sum of ExpectedHours minus the sum of HoursRecorded) aren't including the numbers where 0 time is recorded. I'd like to get the results in the table (above right) and filter out weekends etc. myself.

I've seen similar issues with the totals not adding up but none that have the required numbers to be forced where nulls exist.

Help will be much appreciated!

[hemalpatel · Accepted solution]

I think i've got it! I stayed up till 3am to figure this out and, to my embarrassment, it looks like it should not have taken anywhere near this long to figure out.

So, i kept with a column [ExpectedHours] in the dates table as this number is a daily requirement. The formula being:

DailyExpectedHours = SUM(DimDate_Event[ExpectedHours])+IF(ISBLANK(SUM(DimDate_Event[Date])),7.0,0)

 The IF(ISBLANK... sorted out the additional 7 hours per day in my above post.

The bit that really worked - I used SUMX, SUMMARIZE, CALCULATE to sum up the hours per day, per user for time entries that have been submitted (posted) like so:

HoursPosted = 
SUMX(
    SUMMARIZE(FctTimeEntry, FctTimeEntry[TimekeeperUserID], DimDate_Event[Date]),
    CALCULATE(IF(SUM(FctTimeEntry[Hours])>[DailyExpectedHours],[DailyExpectedHours], SUM(FctTimeEntry[Hours])), FctTimeEntry[Status] = "Posted")+0
)

Then, finally, the subtraction and IF() to avoid negative numbers:

HoursMissing_Posted = 
VAR _MissingHoursTotal = [DailyExpectedHours]-[HoursPosted]
return IF(_MissingHoursTotal < 0, 0, _MissingHoursTotal)

This seems to be working right now though feedback on what i've done would be greatfully appreciated as i'm very much still learning Power BI.

Thanks to @amitchandak for the "+0" tip - don't think i could have worked this out without that.

[Icey]

Hi @hemalpatel ,

Glad to hear that. Please accept your reply as the solution. Your contribution is highly appreciated.

Best Regards,

Icey

[amitchandak]

@hemalpatel ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

This seems correct. Need to check with data

following measures should work

exp = sum(Table[ExpectedHours])+0

rec =Sum(Table[Hoursrecorded])+0

missing = [exp]-[rec]

[hemalpatel]

Thanks, @amitchandak . I've just tried your suggestion - below are the results:

The individual records for ExpectedHours now have 14 on dates where time was recorded (though this should remain 7). Also, it seems the totals are adding up 7 hours for each day where time recordings exist i.e. 7*4 = 35.

I'm new to posting on this forum and couldn't see where to attach a .csv. Below is some tabular data for the two tables i use. I've only put in about two/three weeks worth of data (for two timekeepers) as it's enough to include full 7 hour days, partial days and zero hour days.

[TimeEntry]

TimekeeperID	EventDate	Hours
741	18/10/2020 00:00	0.5
741	19/10/2020 00:00	0.2
741	19/10/2020 00:00	0.4
741	19/10/2020 00:00	0.8
741	19/10/2020 00:00	1.9
741	19/10/2020 00:00	9.2
367	20/10/2020 00:00	0.2
367	20/10/2020 00:00	0.7
367	20/10/2020 00:00	0.8
367	20/10/2020 00:00	1
367	20/10/2020 00:00	1.3
367	20/10/2020 00:00	1.5
367	20/10/2020 00:00	2
741	20/10/2020 00:00	1.9
741	20/10/2020 00:00	9.6
367	21/10/2020 00:00	0.3
367	21/10/2020 00:00	0.4
367	21/10/2020 00:00	1.3
367	21/10/2020 00:00	1.5
367	21/10/2020 00:00	2
741	21/10/2020 00:00	0.6
741	21/10/2020 00:00	8.4
367	22/10/2020 00:00	0.2
367	22/10/2020 00:00	0.4
367	22/10/2020 00:00	0.5
367	22/10/2020 00:00	1.5
367	22/10/2020 00:00	4.6
741	22/10/2020 00:00	0.5
741	22/10/2020 00:00	2.8
367	23/10/2020 00:00	0.3
367	23/10/2020 00:00	0.8
367	23/10/2020 00:00	0.9
367	23/10/2020 00:00	2
741	23/10/2020 00:00	1.4
367	26/10/2020 00:00	0.3
367	26/10/2020 00:00	0.8
367	26/10/2020 00:00	0.9
741	26/10/2020 00:00	4.9
367	27/10/2020 00:00	0.2
367	27/10/2020 00:00	0.3
367	27/10/2020 00:00	0.6
367	27/10/2020 00:00	0.7
367	27/10/2020 00:00	1.6
741	27/10/2020 00:00	4.9
367	28/10/2020 00:00	0.4
367	28/10/2020 00:00	0.5
741	28/10/2020 00:00	8.3
367	29/10/2020 00:00	0.8
367	29/10/2020 00:00	1.4
741	29/10/2020 00:00	7.8
367	30/10/2020 00:00	0.2
367	30/10/2020 00:00	0.4
741	30/10/2020 00:00	3.7
367	02/11/2020 00:00	0.3
367	02/11/2020 00:00	0.4
367	02/11/2020 00:00	0.7
367	03/11/2020 00:00	0.3
367	03/11/2020 00:00	0.4
367	03/11/2020 00:00	1.2
367	05/11/2020 00:00	0.3
367	05/11/2020 00:00	0.4
367	05/11/2020 00:00	1.7
367	06/11/2020 00:00	0.2
367	06/11/2020 00:00	0.3
367	06/11/2020 00:00	0.5
367	06/11/2020 00:00	0.7
367	06/11/2020 00:00	0.8

[DimDate]

Date	IsWeekend
05/10/2020 00:00	FALSE
06/10/2020 00:00	FALSE
07/10/2020 00:00	FALSE
08/10/2020 00:00	FALSE
09/10/2020 00:00	FALSE
10/10/2020 00:00	TRUE
11/10/2020 00:00	TRUE
12/10/2020 00:00	FALSE
13/10/2020 00:00	FALSE
14/10/2020 00:00	FALSE
15/10/2020 00:00	FALSE
16/10/2020 00:00	FALSE
17/10/2020 00:00	TRUE
18/10/2020 00:00	TRUE
19/10/2020 00:00	FALSE
20/10/2020 00:00	FALSE
21/10/2020 00:00	FALSE
22/10/2020 00:00	FALSE
23/10/2020 00:00	FALSE
24/10/2020 00:00	TRUE
25/10/2020 00:00	TRUE
26/10/2020 00:00	FALSE
27/10/2020 00:00	FALSE
28/10/2020 00:00	FALSE
29/10/2020 00:00	FALSE
30/10/2020 00:00	FALSE
31/10/2020 00:00	TRUE
01/11/2020 00:00	TRUE
02/11/2020 00:00	FALSE
03/11/2020 00:00	FALSE
04/11/2020 00:00	FALSE
05/11/2020 00:00	FALSE
06/11/2020 00:00	FALSE