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

clustered column chart count rows where incoming date and resolved date are 2 different bars

I feel like this has to be easy, but I just can't seem to solve it... 

 

I have in my table: Create Month, Resolved Month. team. I want to show a clustered column chart showing me, on one bar, the number created in the create Month, then in the other bar, the number resolved in the resolved month. I want to be able to select a team in the report view it updates the bars based on the team selected.

 

X axis will be the months (jan,feb,mar,etc.), then 2 bars per month, one counting tickets incoming, other counting tickets resolved.

 

Please help!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: clustered column chart count rows where incoming date and resolved date are 2 different bars

Hi @bengisby,

 

You need to create a calendar table and make two inactive relationships with your Created Month and Resolved Month (assuming this is date format).

 

Then just create two measure like this:

Created =
CALCULATE (
    COUNT ( Table[TEAM] );
    USERELATIONSHIP ( Table[Create Month]; Calendar[Date] )
)


Resolved =
CALCULATE (
    COUNT ( Table[TEAM] );
    USERELATIONSHIP ( Table[Resolved Month]; Calendar[Date] )
)

Then add the date from the calendar table as your axis on the clustered column chart, and the measures in values.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: clustered column chart count rows where incoming date and resolved date are 2 different bars

Hi @bengisby,

 

You need to create a calendar table and make two inactive relationships with your Created Month and Resolved Month (assuming this is date format).

 

Then just create two measure like this:

Created =
CALCULATE (
    COUNT ( Table[TEAM] );
    USERELATIONSHIP ( Table[Create Month]; Calendar[Date] )
)


Resolved =
CALCULATE (
    COUNT ( Table[TEAM] );
    USERELATIONSHIP ( Table[Resolved Month]; Calendar[Date] )
)

Then add the date from the calendar table as your axis on the clustered column chart, and the measures in values.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Helper I
Helper I

Re: clustered column chart count rows where incoming date and resolved date are 2 different bars

This worked! Thanks so much.

 

Slight edits: should not be semicolon, should be comma, and also I needed to make a month calendar following this post:

https://community.powerbi.com/t5/Desktop/Calendar-w-months-interval/td-p/232049

 

Highlighted
Helper I
Helper I

Re: clustered column chart count rows where incoming date and resolved date are 2 different bars

HI @MFelix, I also have a table showing each ticket's details. When I click on one of the bar dates in your solution, is there a way to have the table filter to show me only the details for the list of tickets in that YYMM's bar?

Highlighted
Super User III
Super User III

Re: clustered column chart count rows where incoming date and resolved date are 2 different bars

 

Not sure what you want to show in this table but you can add a new measure:

 

 

Month_Select = [Created] + [Resolved]

 

Then in a matrix visual the following fieds:

  • Rows: Team
  • Columns: YYYYMM (Should come from calendar table)
  • Values: Month_Select

Other option is to create two tables with only two columns:

  • Team + Created
  • Team + Resolved

Hide the Created and Resolved columns and this will show two columns withs the open and closed based on the graph selection.

 

See both options below:

 

chart.gif

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

Re: clustered column chart count rows where incoming date and resolved date are 2 different bars

@MFelix I got this to work too, just want to thank you again I really appreciate the help on this. 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors