cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Calculated Column to sum values of a column based on month

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?

 

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

 

Thanks a lot.

 

1 ACCEPTED SOLUTION
Community Champion
Community Champion

@micheleruggieri

 

Hi, try with this:

 

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

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

6 REPLIES 6
Helper II
Helper II

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.

Community Champion
Community Champion

@micheleruggieri

 

Hi, try with this:

 

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

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

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.

@micheleruggieri

 

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

 

Regards

Victor




Lima - Peru

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

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

Thanks!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors