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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
noob8
Frequent Visitor

Time Line Graph Open vs Closed Tickets

I am trying to display a time line that will show the number of tickets closed vs the number of tickets opened by Team Assigned and Owner Assigned (these could be 2 different graphs, my main issues is showing the actual timeline of open vs closed tickets).

 

My Source Data Looks Like This:

Capture.PNG

If the Closed Date Column has an EMPTY cell, its because the incident hasnt been closed.

 

Following some previous suggestions, I have:

 

1. Created a DataTable using: DateTable = CALENDARAUTO(). My understanding is that this will be used in the X Axis of the line chart.

- I formatted the DataTable and the Open and Closed Date Columns as date  to mm/dd/yyyy

 

2. Created two relationships:

- One to Many Direct Relationship from DataTable[Date] to Mock Data[Open Date]

- One to Many Indirect Relationsip from DataTable[Date] to Mock Data[Closed Date]

 

3. Created 3 Measures:

- Closed = COUNTA(Mock Data[Closed Date])

- Open = COUNTA(Mock Data[OpenDate])

- ClosedByDate = CALCULATE([Closed], USERELATIONSHIP(DateTable[Date], Mock Data[Closed Date]))

 

4. Add the Line Chart - I've tried different combinations but just end up with a blank chart.

- Axis: Mock Data[Closed Date]))

- Values: ClosedByDate

 

Any help would be really useful.

 

Thanks!

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

What you have done seems right. Use the Date from the calendar on the chart.

Also, please refer to the solution for a similar problem: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

 

 

 

Thanks - This is one of the solutions I had looked at, but still no luck. Ill continue to try and get it to work

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

@amitchandak Thanks - I have uploaded the sample file here:

 

https://drive.google.com/open?id=1nCka3Uvqxm_Qo7zi-B-03ZyAINWlUUEP

 

 

The types of charts I am expecting to create is something similar to:

 

Time Graph - Id like to be able to distinguish the assigned teams or the assigned owners. 

Id like to be able to see this for each assigned teamId like to be able to see this for each assigned team

 

Orange is Tickets Open and Blue is tix closed. Again it would be useful to see a breakdown by Teams/Owner

Orange is tix opened blue is tix closedOrange is tix opened blue is tix closed

Thank you!

How to get the above variance visulaization?

The dates have time, I created a new column without the time and joined with date dimension. Create some new column in date dimension.

 

https://www.dropbox.com/s/a4pnoz1fj721e6v/Incident_Mock.pbix?dl=0

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

Thanks @amitchandak!

 

This is looking better, in the file you provided how could I add the Teams to the legend? I am assuming this would be the way to view the graph by Team. Also any idea how do go about creating the second bar chart I listed above? or if you have any recommendations that you would use to chart this data?

 

In terms of the columns you added, I had a few questions to help me understand. 

 

Why do we need to create the Month Year and the MonthYearSort columns in the DateTable? I thought by setting the formats of all the dates the same via clicking on the column and choosing the mm/dd/yy format for all it would ignore the hours.

 

You created a Open Not Closed measure with the following, I need some help understanding the FILTER piece: 

Open not closed = CALCULATE(COUNTX(FILTER('Mock Data','Mock Data'[Open DT]<=max(DateTable[Date]) && (ISBLANK('Mock Data'[Close DT]) || 'Mock Data'[Close DT]> max(DateTable[Date]))),'Mock Data'[Incident ID]),CROSSFILTER(DateTable[Date],'Mock Data'[Open DT],None)). 
 
If I read this correctly, the filtering section is filtering Open Dates within the DataTable(Date) range, also counting for any Blanks in the Close DT column, what I am confused is with the section in bold above, would this ever occur?
 
 
Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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