cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Solution Sage
Solution Sage

Count rows per month

Hi All,

Very basic, yet I am strugling to get the correct result.

I would like to create a trend line with countrows per month/year.

Legend should be x-axis year/month. y-axis # of records. Then to create a slicer/filter according to year/month.

For example, the data in the screenshot below wll be transltated to -->

Jan-2019; 3

Dec-2018; 2

Nov-2018;1

Oct-2018; 1

Sep-2018; 0

...

 

I have created a date table.

I have created a measure to count rows in my table.

I linked Commencment date to the date table.

 

But it is not working for some reason.
Either the trend line is not showing, or the legend is not showing or count is by month over years and not per month/year.

Any guidance?

 

Cheers!

table count dates.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

Re: Count rows per month

Hi @AClerk,

 

For you requirement, I have made a test based on your information.

 

Please check if the below image is your desired output.

 

Capture.PNG

 

More details, please refer to the attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Resident Rockstar
Resident Rockstar

Re: Count rows per month

Hi @AClerk,

 

I have a little confused about your scenario.

 

What visual do you want to create? Line chart?

 

If is is convenient, could you share your data sample which could reproduce your scenario and your formula so that I can have a test with your data to get the solution.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Solution Sage
Solution Sage

Re: Count rows per month

Hi,
This is a dummy data below.
I am trying to create a line graph, or bars.

I want the x-axis to show the month/year (Date Column). y-axis to show the count of records in each month/year

I created a measure --> 

CountRecords = COUNTROWS('Table')
 
I also have a Date table, as explained in this blog.
 
Getting all kind of weird results and not the expected line chart.
 
Expecting to get something like this (with relevant data from my table...)
 
line_bar.png
 
 
 
 
 
 
 
 
 
 
 
 
Also it would be nice to have a slicer/filter according to month, year and multiple select.
i.e. can select 2018, Jan, Feb, March. OR 2018 Dec, 2019 Jan, Feb.
etc.
 
Thanks a lot!
 
 
idDateTypeEnd Dateunique_idStatusDay DiffColumn XColumn YColumn Z
115/1/2019R 205Open175.136949-3.910960.586164
28/1/2019L 201Open241.359541-2.323210.60219
324/12/2018S 208Open396.243304-2.396230.982917
418/12/2018S 202Open451.510626-9.756080.456368
516/12/2018L 204Open473.868114-7.354110.89261
614/11/2018L 185Open794.008227-7.626580.141866
731/10/2018L 199Open931.022142-3.66570.579697
831/7/2018L 25Open1859.40272-9.365740.214208
930/7/2018L31/03/1721Closed1868.558588-8.811160.927714
1027/7/2018L 206Open1895.023559-9.985150.521363
116/7/2018L 117Open2105.424869-3.683950.605868
127/6/2018L 181Open2398.132357-4.603330.567406
1329/5/2018L 179Open2484.581901-8.420410.265024
1423/4/2018L 159Open2846.736259-9.821790.275311
1528/3/2018R14/06/1855Closed3107.567686-9.490270.734757
169/3/2018L 83Open3298.056153-0.552060.777119
179/3/2018L 91Open3290.207545-2.619120.578116

 

Highlighted
Resident Rockstar
Resident Rockstar

Re: Count rows per month

Hi @AClerk,

 

For you requirement, I have made a test based on your information.

 

Please check if the below image is your desired output.

 

Capture.PNG

 

More details, please refer to the attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper III
Helper III

Re: Count rows per month

Hello, 

 

I tried to do the same chart than you in your PIBX 

I put the same attributes on X and Y but I can not get the format YYYY-MM like you. I have just months. Can you show how to get this please ?

 

Thank you !

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors