Time Tracking Compliance %

[mscabrera]

Hi everyone,

I'm having troubles to get the Compliance % based on the hours logged per user.

I have a table with the logged hours per person per "transaction/task" called -Hours Logged-, I also have a table with information related to every person called -Resources-, in this table we have the start date and the end Date (I created a calculated column to show the current day [Calc. End Date]).

Since every person is located in different regions, they have different requirements for logging hours, i.e, for the person in region BR needs to log 8 hrs a day, 40 hrs week, which is different from the person in region IN.

I also crated a Date table, for calculation purposes.

There are also another 2 conditions,

1: only count weekdays and

2: if the person log more than the defined hours per day it should show that number like in the following example:

analyst3 logged 10 hrs on Apr 4th, for the compliance% it should be:

Hours Logged= 8hr

Hours Required = 8hrs

Compliance% = 100%

So this was the context, now the problem is when trying to calculate the Compliance% = Hours Logged / Hours Required.

I created this measures (I'm pasting them as images, but you can see them in the attached PBIX file)

HoursLogged = I just wanted to summarize by day the hours logged

Hours Required = Here I wanted to count the number of weeks every person has worked and then multiply it by the hours required per analyst

Compliance = here to solve the issue with the totals I decided to calculte the average

with those measures I'm gettin the following:

Which might look right, but it isn't because in the case of the screenshot, the analst3 started the first of march, but the report is only showing me the compliance from the 3rd week of march, so its excluding the days where the person didn't logged any hours.

At that moment I thought that might have something to do with the granularity of the data, because I have the hours logged by day, but I only have a number (scalar value) for the hours required, that's when I decided to try to create a virtual table to replicate the dates acorrding to the hours required and that's how I ended up creating the following measure:

Virtual table Hours Required =

Unfortunatly it didn't work. Using this new measure and calculating the compliance, I'm getting this:

The expected result should be (example for the Analst3) something like this (including the changes made for the days where he logged more hours than the required:

As always your help will be much appreciated

Link to PBI file: https://we.tl/t-fhFp10JS9i

[mscabrera]

Hi @twi1 

Tamar, that was exactly what I was looking for, thank you so much for your help!!

I'm still trying to understand the approach you took to solve the problem 

Regards

[twi1 · Accepted solution]

Hi again @mscabrera , 
here is modified code for measure and it works i tested it :

w - HoursLoggedchemi1 =
VAR SummarizedTable =
SUMMARIZE(
'Hours Logged',
Dates[Date],
'Hours Logged'[Name],
"aggregHours",
VAR CurrentName = 'Hours Logged'[Name]
VAR DailyHours = LOOKUPVALUE(Resources[Daily Hours], Resources[Name], CurrentName)
RETURN IF(SUM('Hours Logged'[Timesheet Hrs]) > DailyHours, DailyHours, SUM('Hours Logged'[Timesheet Hrs]))
)
RETURN
IF(
ISBLANK(SUMX(SummarizedTable, [aggregHours])),
0,
SUMX(SummarizedTable, [aggregHours])
)

Are there any further questions or it is the final solution, if it is final solution please mark it as a solution,
Best regards

[mscabrera]

Hi @twi1 
Thank you for your response, Tamar. I tried the measure you shared and it works great but there's one missing part, and it is when validating if : IF(SUM('Hours Logged'[Timesheet Hrs])>8. The problem here is that it shouldn't be an static number should be comparing the Daily Hours (every person may have different number of required hours, thus it only validate against 8 hours which will be only the case for the Anlyst3, the other ones will get a lower compliance%. I tried to solve it by modifying your solution like this:

but unfortunatly in some cases it's not showing the correct number of hours logged

case:

All analyst in march 15

Analyst1: Logged 8hrs. but the max/required for him is 7 hrs a day, so the condition apply and change the hours to 7

Analyst2: Logged 4hrs. max/required for him is 7 hrs a day, so the conditional doesn't apply here

Analyst3: Logged 8hrs. max/required for him is 8 hrs a day, so the conditional doesn't apply here

The Analyst4 started in march 20th, so it doesn't count

Once I lift the analyst filter I'm getting this:

Which is not quite ok, becasue the total number of hours logged of the 3 analysts should be 19

Analyst1: 7hrs (because we applied the conditional)

Analyst2: 4hrs (no need to apply conditional)

Analyst3: 8hrs (no need to apply conditional)

Total: 19hrs Logged / 22 hrs Required = 86,36%

not 20hrs Logged / 22hrs required = 90.91% that the table is showing.

To clarify the daily maximum hours required per person are the ones on the column Resource[Daily Hours]

How do you think we can handle that situation?

PS ('m also applying a visual filter to the page to not include saturdays and sundays)

Again, thank you so much for any help/guidance  you can provide

Regards

[twi1]

Hi @mscabrera,

Measures of Hours required and compliance are good. I would suggest modifing hours logged measure here like this, to work on blank rows (rows where you do not have entries) as 0 also it will perceive values that will be greater than 8 daily as 8, as you said it is required in question  and it will calculate the compliance  :

w - HoursLoggedchemi =

IF(

    ISBLANK(

        SUMX(

            SUMMARIZE(

                'Hours Logged',

                Dates[Date],

                "Sum", IF(SUM('Hours Logged'[Timesheet Hrs])>8, 8, SUM('Hours Logged'[Timesheet Hrs]))

            ),

            [Sum]

        )

    ),

    0,

    SUMX(

        SUMMARIZE(

            'Hours Logged',

            Dates[Date],

            "Sum", IF(SUM('Hours Logged'[Timesheet Hrs])>8, 8, SUM('Hours Logged'[Timesheet Hrs]))

        ),

        [Sum]

    )

)

Main issue was to calculate compliance % when 0 that will be resloved by abovementioned code , however, Please tell me if my response helped you and if you need more assistnace with regard to anything else contact me there and I can help you anytime, for example if you tell me which days do you want to be included for example in week 1 of march ,

then we can correct date presentation on matrix because calendar has first and second of march as Friday and Saturday that causes representation problems on matrix,

I am looking forward to your response,

Best Regards,
Tamar