Forum Discussion

mscabrera's avatar
mscabrera
Helper I
2 years ago
Solved

Time Tracking Compliance %

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 hav...
Need Help
  • twi1's avatar
    twi1
    2 years ago

    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's avatar
mscabrera
Helper I
2 years ago

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