Showing values in clustered column chart

[ghouse_peer]

Hello Guys,

 I have a task where i need to show Tickets Opened and tickets Closed in Clustered column chart.

I have  data lik this: 1) Table Name: Report1, Columns: Ticket Opened Date(Date), Ticket closed date(Date), Ticket Number(whole Number)

2) Table Name(Created Using Dax Calendar): Dates( contains date from start to end)

So task is using clustered Column chart. I need to show  all 12 months in x axis and Count of tickets Opened/Closed according to month(i,e 1 column showing Tickets opened and another column showing Tickets Closed). and in Y axis units should be in thousands.

I tried using this for creating a seperate column : 

CALCULATE(COUNT('Report 1'[Ticket Number]),FILTER('Report 1','Report 1'[Ticket Closed Date]))

but failed. 

Kindly provide the solution. Thanks in advance

[MFelix]

Hi @ghouse_peer ,

Create a relationship between the calendar table and the Opened Date and the Closed Date. depending on the order you make it one will be active and the other inactive then just create the following measures:

Open Tickets = COUNT(Report1[Ticket Number])

Closed Tickets = CALCULATE ( COUNT(Report1[Ticket Number]), USERELATIONSHIP(Report1[Ticket Clodes Date],Calendar[Date] ) )

Now place both measure in the chart using the calendar as the X-Axis.

[ghouse_peer]

Hello @MFelix 

 I have done as said, But i am getting the solution as in  the pic.

After doing the above calculations:

1) Selected the both measures in the values.( Tried for both clustered column chart and Stacked bar chart)

2) Selected Dates table in Axis.

3) Selected Dates table in Slicer.

Issue 1: So when i selected the year in slicer i am getting only opened Tickets for 2017. Whereas i have data for all the years for both Opened and closed Tickets.

Issue 2: I need to get all 12 months on the axis.

Kindly Help...

Thank you

[MFelix]

Hi @ghouse_peer ,

Can you share the measure formula and an image of your relationships?