Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ghouse_peer
Post Patron
Post Patron

Tickets Closed and Opened Clustered Column Chart

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: Dates.JPG

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

Ticket.JPG

 

 

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
1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

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.

 

T 1.jpg

 

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

 

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

 

T 2.jpg

 

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.

 

T 3.jpg

 

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,

 

T 4.jpg

 

T 5.jpg

 

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.

View solution in original post

13 REPLIES 13
v-zhenbw-msft
Community Support
Community Support

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.

 

T 1.jpg

 

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

 

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

 

T 2.jpg

 

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.

 

T 3.jpg

 

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,

 

T 4.jpg

 

T 5.jpg

 

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.

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.

 

                                                              

 MayjuneJuly
Opened Tickets104020
Closed Tickets202010
Backlog305060
    
LogicC7+(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.

 

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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. 

@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.

Hi @ghouse_peer ,

 

It is my pleasure.😊

 

Best regards,

 

Community Support Team _ zhenbw

v-zhenbw-msft
Community Support
Community Support

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]))

 

Ticket 1.jpg

 

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

 

Ticket 2.jpg

 

Ticket 4.jpg

 

Ticket 3.jpg

 

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.

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
Microsoft Employee
Microsoft Employee

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

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hello @mahoneypat @lbendlin 

 

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

2017 data.JPG

 

2016 Data.JPG

 

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
Super User
Super User

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.

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

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.