Forum Discussion
How to find Ticket Backlog?
Hi Friends,
Can anyone help me to create a table like below in Power BI Desktop.
The data is look like below,
Thanks in Advance,
Maadi.
14 Replies
Can you please define what you mean by [Total Tickets] and [Pre Month tickets (Backlog)] ?
I'm guessing [New tickets] are just the count of records created in the Month and like-wise [Closed] represents the number closed in the month.
Oh and any chance you can post a bit more than 5 lines as sample data. It would be easier to check with more data.
I'll give it a crack anyway. Try adding these 4 measures to your Ticket table.
You'll need two relationships between 'Tickets' and the 'Date' table.
The active relationship should be between 'Tickets'[Opened On] while the inactive relationship should be between 'Tickets'[Closed On]
New Tickets = COUNTROWS('Tickets') Closed Tickets = CALCULATE(COUNTROWS('Tickets'),USERELATIONSHIP('Tickets'[Closed On],'Dates'[Date])) Pre Month Tickets (Backlog) = CALCULATE( COUNTROWS('Tickets') , FILTER( ALL('Dates'[Date]), 'Dates'[Date] < MIN('Dates'[Date]) ) ) - (CALCULATE( COUNTROWS('Tickets') , USERELATIONSHIP('Tickets'[Closed On],'Dates'[Date]), FILTER( ALL('Dates'[Date]), 'Dates'[Date]<MIN('Dates'[Date]) ) )-1) Total Tickets = [New Tickets] + [Pre Month Tickets (Backlog)]How does that look?
