Calculate SUM of Hours in week

[Anonymous]

Hello All,

I have got a table which has Employee Name, Date and Hours Worked. See data below - 

Date	Employee Name	Week Number	Hours Worked	Expected Result
02/11/2020	ABC	7	2	34
02/12/2020	ABC	7	2	34
02/11/2020	ABC	7	3	34
02/12/2020	ABC	7	3	34
02/13/2020	ABC	7	8	34
02/14/2020	ABC	7	8	34
02/15/2020	ABC	7	8	34
02/11/2020	XYZ	7	8	42
02/12/2020	XYZ	7	8	42
02/13/2020	XYZ	7	8	42
02/14/2020	XYZ	7	8	42
02/15/2020	XYZ	7	10	42

So what I would like to do is calculate the hours by ABC in week 7. The week starts on Thursday. So on and so forth.

Thanks in Advance.

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Try this formula please.

Column = CALCULATE(SUM('Table'[Hours Worked]),FILTER('Table','Table'[Employee Name]=EARLIER('Table'[Employee Name])&&'Table'[Week Number]=EARLIER('Table'[Week Number])))

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[az38]

Hi @Anonymous 

if I understand you correct you need a measure like

CALCULATE(SUM(Table[Hours Worked]), ALLEXCEPT(Table, Table[Employee Name], Table[Week Number]) )

[Anonymous]

@az38 I am not looking to add a measure. I need a calculated column.

[az38]

@Anonymous 

it will have completely the same formula

[Anonymous]

@az38 I had already tried this solution. It gives incorrect solution.

For example, for employee ABC the total hours come out as 40. The answer is same for Column and measure.

[FrankAT]

Hi @Anonymous,

the measure or formula for the calculated column of @az38 with your sample data is correct:

Regards FrankAT

[Anonymous]

@FrankAT & @az38  what could affect a different result. 

Let's suppose I have more columns than this and multiple rows are not the same but Employee Name, Week Number is the same. Is that the reason I am not getting the correct result.

[az38]

@Anonymous 

it shouldn't be as ALLEXCEPT() remove all filters except Employee Name and Week Number

Check your data first.