Calculated Column to sum values of a column based on month

[micheleruggieri]

Dear all,

  I'm trying to create a calculated column ('Event_Month') that would sum up all "1" on a given column based on the month. For example, below is just January data. The sum of the "1" in Event is 8 for January. The column 'Event_Month' should just count the total for the matching month at every cell. Does anyone know how to do it?

Date	Event	Event_Month
1-Jan-17	0	8
2-Jan-17	0	8
3-Jan-17	0	8
4-Jan-17	0	8
5-Jan-17	1	8
6-Jan-17	0	8
7-Jan-17	0	8
8-Jan-17	0	8
9-Jan-17	1	8
10-Jan-17	0	8
11-Jan-17	0	8
12-Jan-17	1	8
13-Jan-17	0	8
14-Jan-17	0	8
15-Jan-17	1	8
16-Jan-17	0	8
17-Jan-17	0	8
18-Jan-17	0	8
19-Jan-17	1	8
20-Jan-17	1	8
21-Jan-17	0	8
22-Jan-17	0	8
23-Jan-17	0	8
24-Jan-17	1	8
25-Jan-17	0	8
26-Jan-17	0	8
27-Jan-17	0	8
28-Jan-17	0	8
29-Jan-17	0	8
30-Jan-17	1	8
31-Jan-17	0	8

Thanks a lot.

[micheleruggieri]

It would also be acceptable that the calculated column increases the counts at every row if there is a "1" in the event column, so that at the end of the month there would be the number "8". Thanks.

[Vvelarde · Accepted solution]

@micheleruggieri

Hi, try with this:

Event_Month =
CALCULATE (
    SUM ( Table2[Event] ),
    FILTER ( Table2, MONTH ( Table2[Date] ) = MONTH ( EARLIER ( Table2[Date] ) ) )
)

Regards

Victor

Lima - Peru

[micheleruggieri]

Hi Victor,

 If I add another input column with customer names, is it possible to add a filter so that the count is also based on the customer as well as the month? Thank you.

[Vvelarde]

@micheleruggieri

Hi, yes. working with Earlier  you can obtain this.

Regards

Victor

[micheleruggieri]

Thanks. Sorry could you draft an example for me (not very familiar with DAX), based on the below?

Date	Event	Operator
1-Jan-17	0
2-Jan-17	0
3-Jan-17	0
4-Jan-17	0
5-Jan-17	1	A
6-Jan-17	0
7-Jan-17	0
8-Jan-17	0
9-Jan-17	1	B
10-Jan-17	0
11-Jan-17	0
12-Jan-17	1	A
13-Jan-17	0
14-Jan-17	0
15-Jan-17	1	B
16-Jan-17	0
17-Jan-17	0
18-Jan-17	0
19-Jan-17	1	B
20-Jan-17	1	C
21-Jan-17	0
22-Jan-17	0
23-Jan-17	0
24-Jan-17	1	C
25-Jan-17	0
26-Jan-17	0
27-Jan-17	0
28-Jan-17	0
29-Jan-17	0
30-Jan-17	1	B
31-Jan-17	0

[micheleruggieri]

Thanks!