How to show data per week per employee?

[Musky]

I want to make a dashboard that shows the work goal per week. I have an excel sheet with all the dates and hours worked per day per employee and want this to be visualized in Power BI. The following 2 sheets are the 2 tables I have as I mentioned:
Sheet with all the hours worked per day (with dates etc, goes back to 2010)This is where I've set the goal for each employee

I linked them in power BI desktop and tried some things out myself but couldn't find the solution. Is there anyone that could help me with this? I would really appreciate this!

[v-huizhn-msft]

Hi @Musky,

First, you should use WEEKNUM function to get the week number based on date. Create a calculated column using the formula below.

weeknum = WEEKNUM(FactTable[Date])

Second, create an employee’s total work hour in each week. Create a measure using the following formula.

hours in one week=CALCULATE(SUM(FactTable[hour]),ALLEXCEPT(FactTable,FactTable[weeknum]))

Third, create a relationship between actual table and target table.

Finally, create a table, select Users_Id, “hours in one week” measure, Goal field as value level, you will get what you want.

Best Regards,
Angelia

[Musky]

Thanks for replying v-huizhn-msft,

I've done the first step, but when entering the measure at step 2 I get the following error:

Your code used "FactTable" three times, do I have to call my main table with everthing in there three times on those places?

Thanks in advance,

Musky~ 

[v-huizhn-msft]

Hi @Musky,

Sorry, I misplace the parenthesis in SUM function in formula, the right should be the following one.

hours in one week=CALCULATE(SUM(Hours[Aantal_Uren]),ALLEXCEPT(Hours,Hours[weeknum]))

Best Regards,
Angelia