Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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] ) ) ) )
Does it need to be in 1 column? And are you allowed to use measures?
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] ) ) ) )