Forum Discussion

Domenick's avatar
Domenick
Helper IV
6 years ago

[Help!] Comparing Expected Hours Table to Time Log

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...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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.