Forum Discussion
Multiple Line Chart from Table with 2 Dates
I'm trying to create a Line Chart with 2 lines based on the same table.
My table is a list of help desk tickets with unique key being the ticket ID #. Each Ticket has a Created Date and a Resolved Date.
How can I create a Line Chart where X axis is the past 12 months, and one line shows the number of tickets created for that month, and the second line shows the number of tickets resolved for that month?
6 Replies
Ensure your table has at least the following columns:
TicketID
CreatedDate
ResolvedDateCreate a Date table to use for the X-axis. This table should cover the range of dates you are interested in (e.g., the past 12 months).
DateTable = CALENDAR(DATE(YEAR(TODAY())-1, MONTH(TODAY()), 1), TODAY())
Relate the CreatedDate column in your tickets table to the Date column in the Date table.
Relate the ResolvedDate column in your tickets table to the Date column in the Date table.Tickets Created = COUNTROWS(FILTER('Tickets', 'Tickets'[CreatedDate] = 'DateTable'[Date]))
Tickets Resolved = COUNTROWS(FILTER('Tickets', 'Tickets'[ResolvedDate] = 'DateTable'[Date]))Add a Line Chart visual to your report.
Drag the Date column from the Date table to the X-axis.
Add the Tickets Created measure to the Values field well.
Add the Tickets Resolved measure to the Values field well.Ensure the X-axis is set to display the past 12 months.
Customize the lines and markers as needed for better visualization.Does it matter that my CreatedDate and ResolvedDate fields are actually Date + Time?
For example, the value for one of these fields for one of these records would be 2/25/2025 3:00:00 PM
Also you had in your instructions:
Tickets Created = COUNTROWS(FILTER('Tickets', 'Tickets'[CreatedDate] = 'DateTable'[Date]))
Tickets Resolved = COUNTROWS(FILTER('Tickets', 'Tickets'[ResolvedDate] = 'DateTable'[Date]))What does this mean? Do I have to create a new field?
You would need a role playing date dimension.
So the date dimension will have a realtionship to each date, one active and one inactive. Your one measure would be the count using the active relationship. The second would need to activate the inactive relationship with calculate( , USERELATIONSHIP() ).
Then can plot the date from the date dimension on the x axis and the measures on the y axis
Hi rickas27
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi rickas27
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi rickas27
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
