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

[bengisby]

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!

[MFelix · Accepted solution]

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

[bengisby]

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?

[MFelix]

• Hi @bengisby,

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:

Regards,

MFelix

[bengisby]

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

[bengisby]

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