cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manicktvs91
New Member

How to count month wise from a list of data contains dates?

Hi

I have the following set of data queried in power query. 

Date of complaintCustomer nameComplaint detailResolution status
1/17/20aaaYes
1/18/20bbbNo
2/10/20cccNo
2/15/20dddNo
2/18/20eeeYes
3/16/20fffYes
4/13/20gggYes
4/17/20hhhNo
4/21/20iiiYes
4/31/20jjjNo
12/5/20kkkYes
12/7/20lllNo

 

I need to show in a line graph - x axis : month names in format Jan20, Feb20, Mar20...Dec20

y axis : number of complaints in each month added cumulatively

 

Output in line graph based on the above table should be like

Jan20 : 2

Feb20 : 2 + 3 = 5

Mar20 : 5 + 1 = 6

Apr20 : 6+4 = 10

May 20 : 10 + 0 = 10

Jun20 : 10 + 0 = 10

upto

Dec20 : 10 + 2 =12

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @manicktvs91 

Please check the below picture and the sample pbix file's link down below.

 

Picture2.png

 

Complaints count cumulate =
CALCULATE (
SUMX ( VALUES ( Dates[Month & Year] ), CALCULATE ( COUNTROWS ( Complaint ) ) ),
FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

 

 

https://www.dropbox.com/s/jzx99o8dp3f1x1i/manicktvs.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi, @manicktvs91 

Please check the below picture and the sample pbix file's link down below.

 

Picture2.png

 

Complaints count cumulate =
CALCULATE (
SUMX ( VALUES ( Dates[Month & Year] ), CALCULATE ( COUNTROWS ( Complaint ) ) ),
FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

 

 

https://www.dropbox.com/s/jzx99o8dp3f1x1i/manicktvs.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

This is fantastic! You are genius and awesome. I have some more queries and I will post here @Jihwan_Kim 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!