Calculate event count per month-year and operator

[Anonymous]

Dear all,

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

Date	Event	Operator	Event_Count
1-Jan-17	0
2-Jan-17	0
3-Jan-17	0
4-Jan-17	0
5-Jan-17	1	A	2
6-Jan-17	0
7-Jan-17	0
8-Jan-17	0
9-Jan-17	1	B	4
10-Jan-17	0
11-Jan-17	0
12-Jan-17	1	A	2
13-Jan-17	0
14-Jan-17	0
15-Jan-17	1	B	4
16-Jan-17	0
17-Jan-17	0
18-Jan-17	0
19-Jan-17	1	B	4
20-Jan-17	1	C	2
21-Jan-17	0
22-Jan-17	0
23-Jan-17	0
24-Jan-17	1	C	2
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	4
31-Jan-17	0

Thank you.

[BeemsC]

Does it need to be in 1 column? And are you allowed to use measures?

[Anonymous]

@BeemsC unfortunately it has to be a calculated column...

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Try this Calculated Column

EventCount =
COUNTROWS (
    FILTER (
        TableName,
        TableName[Operator] = EARLIER ( TableName[Operator] )
            && TableName[Event] = 1
            && MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
            && YEAR ( TableName[Date] ) = YEAR ( EARLIER ( TableName[Date] ) )
    )
)

[Anonymous]

@Zubair_Muhammad Thanks! It works.