[Help!] Comparing Expected Hours Table to Time Log

[Domenick]

I want to display a matrix of available hours per day for each employee that can roll up into week & month. I have a table of hours that are logged by project and day (so each day might have multiple records for each employee). In a separate table, I have a list of each employee and their expected hours per day.

My plan was to compare that table with a sum of hours per day for each employee. I was able to calculate that for each day, but I run into a problem when an employee has multiple records per day. In that situation, my formula will also sum expected hours per day. I've worked around this with a MAX function so that the Expected hours per day is only compared once to the sum of actual hours. Unfortunately, that makes rolling the data up into weeks and months impossible because the sum of all those days will still only be compared to expected hours per day.

I need to create a measure where the sum of hours for each time frame is subtracted from the expected hours per day that is appropriate for that time frame. In the example below, for example, the four hours that Bob worked on 11/1 is subtracted from his expected 8 hours per day to get 4 available hours. I also need to be able to roll this up so that we can see that Bob has 12 available hours for the week of 11/1. 

Anybody have any suggestions on the best approach?

[v-juanli-msft]

Hi @Domenick 

I modify my pbix and finally get the result below:

If it is your expected result, please follow steps below:

1. In edit queries, create a new query,then Close&&apply

2. create relationships, then create a calculated column in "new query" table,

hours/day =
CALCULATE (
    SUM ( 'Planned Hours'[Hours] ),
    FILTER (
        'Planned Hours',
        'Planned Hours'[Employee] = EARLIER ( 'Planned Hours'[Employee] )
            && 'Planned Hours'[Date] = EARLIER ( 'Planned Hours'[Date] )
    )
)


hours/day 2 =
LOOKUPVALUE (
    'Planned Hours'[hours/day],
    'Planned Hours'[Employee], 'new query'[Employee],
    'Planned Hours'[Date], 'new query'[date]
)

3. create measures, then create visuals.

Measure 2 = SUM('new query'[Expected Hours Per Day])-SUM('new query'[hours/day])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Domenick]

Thank you so much for taking the time to help. Your solution isn't working for me, and I'm not sure how. I'm having trouble understanding what you did in your second response. I'm not sure how some of the functions work, like EARLIER. 😥 But I do appreciate the time you took. I hope I figure something out soon.

[v-juanli-msft]

Hi @Domenick 

With the relationships below

Create measures in Planned Hours table,

measure_Hours Per Day = SUM('Employee Details'[Expected Hours Per Day])

planned hours = SUM('Planned Hours'[Hours])

result = SUMX('date',[sub])

To fill the blank cell with values need more transformations, i will update it later.

Please first check if my first reply solve your problem partly.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.