cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AClerk
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

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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

 

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

Anonymous
Not applicable

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors