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.

 

  • Domenick's avatar
    Domenick
    Helper IV
    6 years ago

    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.