DAX - SUM in a calculated column

[tiawy]

Hi

How can I sum in a calculated column (not in a calculated measure)

If I for instance have the following table

EmployeeID	Date	HourRegistration
1	01-01-2015	7
1	02-01-2015	5
1	03-01-2015	6
2	01-01-2015	3
2	02-01-2015	2
1	01-02-2015	7
1	02-02-2015	4
2	01-02-2015	3
2	02-02-2015	2

<tbody>
</tbody>

How do I make a Calculated Column that sums the total hours an employee as registered for the certain month. For instance EmployeeID 1 has registered a total of 18 hours for january 2015, so every row for employeeID 1 and a date in january 2015 should have 18 in this calculated column.


Like this:

EmployeeID	Date	HourRegistration	EmployeeMonthTotal
1	01-01-2015	7	18
1	02-01-2015	5	18
1	03-01-2015	6	18
2	01-01-2015	3	5
2	02-01-2015	2	5
1	01-02-2015	7	11
1	02-02-2015	4	11
2	01-02-2015	3	5
2	02-02-2015	2	5

<tbody>
</tbody>

 

[scottsen]

Must. Not. Ask. Why.

=CALCULATE(SUM(Table1[HourRegistration]), ALL(Table1), Table1[EmployeeId] = EARLIER(Table1[EmployeeId]))

 

[tiawy]

Hi scottsen


Thank you for the quick reply. The formula however gives me the following, which is grandtotal for each employee for all months.


I would like to see the total for each employee for each month


This is what I get with the formula:


EmployeeID Date HourRegistration EmployeeMonthTotal
1 01-01-2015 7 29
1 02-01-2015 5 29
1 03-01-2015 6 29
2 01-01-2015 3 10
2 02-01-2015 2 10
1 01-02-2015 7 29
1 02-02-2015 4 29
2 01-02-2015 3 10
2 02-02-2015 2 10


How do I extend the formula with date?


Thanks a lot!

 

[Kazlik]

I guess you could make another column for Month =FORMAT(Table1[Date], "MM") and then do this

=CALCULATE(SUM(Table1[HourRegistration]), FILTER(Table1, Table1[EmployeeId] = EARLIER(Table1[EmployeeId]) && Table1[Month] = EARLIER(Table1[Month])))

 

[scottsen]

But you are making Measures feel bad.

 

[Kazlik]

Agreed I did feel a bit bad with my recommendation Scott.