Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@Anonymous
Hi, try with this:
Event_Month = CALCULATE ( SUM ( Table2[Event] ), FILTER ( Table2, MONTH ( Table2[Date] ) = MONTH ( EARLIER ( Table2[Date] ) ) ) )
Regards
Victor
Lima - Peru
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.
@Anonymous
Hi, try with this:
Event_Month = CALCULATE ( SUM ( Table2[Event] ), FILTER ( Table2, MONTH ( Table2[Date] ) = MONTH ( EARLIER ( Table2[Date] ) ) ) )
Regards
Victor
Lima - Peru
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.
@Anonymous
Hi, yes. working with Earlier you can obtain this.
Regards
Victor
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 |
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |