Connect a static table with a dynamic table

[Anonymous]

Hello everyone,

I have a static table with EmployeeID and their hours per week, which is based on their contract. Every employee's hours are registrered per day. I want to be able to compare the hours per week and their actual hours they've worked. And not only per week, but also per month or year. So the static table have to become more dynamic. Is this possible?  

[ERD]

Hi @Anonymous ,

In general,

• connect these 2 tables by EmployeeID column
• create a separate Date table with all levels you need (week#, month, year, etc)
• connect your dynamic table to the Date table by Date column
• create measures and use them in visuals.

If you want some particular measure, then, please, provide:

1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1 to 2.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Anonymous]

Hi @ERD , 

It would be great if you could provide me the measure. I created a sample set. You can find the link here:

https://docs.google.com/spreadsheets/d/1Onv2UBeODDCfLOPiOOZUABiDrAtVyDkZ/edit?usp=sharing&ouid=11807...

I added a date table by using the query in this link: 

https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390

I hope it's possible to compare the Hours per week in 'employeeID' with Hours worked in 'sample'. I added a sheet, named 'result'. I hope this helps. If you have any question, please let me know. 

I also tried the following measure:

Hours employee by contract =

SUMX(

VALUES ( 'sample'[EmployeeID]),

DATEDIFF ( MIN ( 'sample'[Date] ), MAX ( 'sample'[Date] ), WEEK )

* MAX ( employeeID[Hours per week] )

)

Unfortunately, this measure didn't work, because it didn't responded well when I added a week filter. For example, when I selected 1 week, the hours returned was 0. 

[ERD · Accepted solution]

@Anonymous ,

I've used 3 measures to get your result:

H_worked = SUM ( 'T-fact'[Hours worked] )

Hours_per_week = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( 'T-fact', 'T-fact'[EmployeeID], 'Date'[Week of Year] ),
        "@H/week",
            VAR currentEmpID = CALCULATE ( SELECTEDVALUE ( 'T-fact'[EmployeeID] ) )
            RETURN
                CALCULATE (
                    SUM ( 'T-contract'[Hours per week] ),
                    'T-contract'[EmployeeID] = currentEmpID
                )
    )
RETURN
    SUMX ( _t, [@H/week] )

Utilisation Rate = DIVIDE ( [H_worked], [Hours_per_week] )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Anonymous]

@ERD , works like a charm!! Thank you so much.