Tickets Closed and Opened 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)

Reference: 

----------------------------

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

[v-zhenbw-msft · Accepted solution]

Hi @ghouse_peer ,

We can use the following steps to meet your requirement.

1. First step is to close the auto date/time function in Data load.

2. Create month column and year column in date table.

Month = MONTH('Date'[Date])
Year = YEAR('Date'[Date])

3. Then we can create two relationships between two tables, the date to open date is active and the date to closed date is inactive.

4. We can create two measures to calculate the open date count and closed date count.

Open = CALCULATE(DISTINCTCOUNT('Table'[Ticket Number]))

Closed = CALCULATE(DISTINCTCOUNT('Table'[Ticket Number]),USERELATIONSHIP('Date'[Date],'Table'[Ticket Closed Date]))

Then create a column chart and put them in it, the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ghouse_peer]

Hello @v-zhenbw-msft  @mahoneypat @lbendlin 

for the same i need to add one more query now.

1) we have done with opened and closed tickets

2) i need to show backlog count now in the same way using line and clustered column chart.(Line values)

3) Backlog count logic: [(Total opened tickets for the month) - (Total closed tickets for the month)]+ Previous month difference calculated same way.

ex: consider excel logic, need same way in PBI.

	May	june	July
Opened Tickets	10	40	20
Closed Tickets	20	20	10
Backlog	30	50	60
Logic	C7+(D5-D6)

June: 40-20=20                                                     July=20-10=10

         20+30(May backlog)=50(June Backlog)              10+50=60 

Kindly help me with solution.

[mahoneypat]

I would propose a different way to calculate your backlog at any given time.  This uses ALL to remove all filters from your date table, so you relationships don't matter on this one.

Backlog =
VAR __mindate =
    MIN ( 'Date'[Date] )
VAR __maxdate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Tickets ),
        ALL ( tickets ),
        Tickets[CreatedDate] <= __maxdate,
        Tickets[CloseDate] >= __mindate
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ghouse_peer]

Hello @mahoneypat 

 I am getting the result, but are you sure you r adding previous month value as i said in question. 

Pls let me know. 

[ghouse_peer]

@v-zhenbw-msft Thank u very much , got the result as expected. Appreciate your work to explain the solution. Really Thank u.

@mahoneypat @lbendlin  Thank you guys for the effort u showed to arrive at a solution.

[v-zhenbw-msft]

Hi @ghouse_peer ,

It is my pleasure.😊

Best regards,

Community Support Team _ zhenbw

[v-zhenbw-msft]

Hi @ghouse_peer ,

We found that Date[Date] and Report1[Ticket Opened] are not exactly the same, so we cannot create a fully effective relationship.

So we suggest you can create two date column in Report1.

Opened_date = DATE(YEAR('Table'[Ticket Opened Date]),MONTH('Table'[Ticket Opened Date]),DAY('Table'[Ticket Opened Date]))

Closed_date = DATE(YEAR('Table'[Ticket Closed Date]),MONTH('Table'[Ticket Closed Date]),DAY('Table'[Ticket Closed Date]))

Then we can create two relationships between date table and Table based on Opened_date and Closed_date.

Because the relationship, the axis will only display the month with data.

If you do not have data for twelve months, then the axis will not display all twelve months

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data?

It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

Please don't contain any Confidential Information or Real data in your reply.
BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ghouse_peer]

Hello,

Kindly find the attached link that contains sample data.

Link:  https://1drv.ms/u/s!AjrlQdwEVeqqhT1RSYK791KQU5AG?e=e9tklL

Task: I have one column with ticket number. Same ticket number has 2 columns associated with it lik Ticket opened date, Ticket Closed Date.

Another table: Date (Created using Dax calendarauto() )

so, when i select a year in slicer. For respective year it should show me the count of tickets opened and Count of Tickets closed in Stacked/Clustered column chart.(Refer Pic attached). Axis showing the 12 months. Ex: x axis 'Jan' should contain 2 column(1 opened ticket count another with Closed ticket count)

Kindly help me with Relationships/Dax/Steps to arrive at solution. Please provide solution as soon as possible. Thank you in advance

[mahoneypat]

You will need to use the USERELATIONSHIP() function for this one.  As @lbendlin suggested, make two relationship between your Date and Report1 tables.  The first to open date (Active, solid line) and the second to closed date (Inactive, dotted line).  And then use two measures like this in your stacked bar chart (with Date table columns on X axis).

Open Orders = DISTINCTCOUNT(Report1[TicketNumber])

Closed Orders = CALCULATE(DISTINCTCOUNT[Report1[TicketNumber]), USERELATIONSHIP('Date'[Date], Report1[ClosedDate])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ghouse_peer]

Hello @mahoneypat @lbendlin 

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

[lbendlin]

You need to us two relationships between your fact table and the dates table, one for open timestamp and the other for close timestamp.

This can be achieved in a multitude of ways, one would be to create a reference of the dates table.

In your clustered column chart you need to make sure to use the date from the dates table for the X axis, NOT the dates from the fact table.

[ghouse_peer]

Hello,

 Can you please elaborate me with the steps (Calculation/Relationships). So that i will follow the same to reach exact solution. Kindly help.

Thank you

[lbendlin]

come to think of it it might be easier to split your fact table into two - one for the opened tickets and one for the closed tickets. That way you only need one Dates table.