Forum Discussion
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:
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
- amitchandakSuper 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-trend/ba-p/882970
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- AnonymousNot applicable
Thanks - This is one of the solutions I had looked at, but still no luck. Ill continue to try and get it to work
- amitchandakSuper User
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-trend/ba-p/882970
- Ashish_MathurSuper User
Hi,
Share some data and show the expected result.