cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AClerk New Contributor
New Contributor

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
Community Support Team
Community Support Team

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

3 REPLIES 3
Community Support Team
Community Support Team

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.
AClerk New Contributor
New Contributor

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

 

Community Support Team
Community Support Team

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 180 members 2,074 guests
Please welcome our newest community members: