cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MaadiKemko
Frequent Visitor

How to find Ticket Backlog?

 

Hi Friends,

 

Can anyone help me to create a table like below in Power BI Desktop.

 

Backlog.PNG 

 

 

 

 

 

 

 

The data is look like below,

Data.PNG

 

 

 

 

 

 

 

 

 

 

Thanks in Advance,

Maadi.

14 REPLIES 14
Microsoft Phil_Seamark
Microsoft

Re: How to find Ticket Backlog?

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Microsoft Phil_Seamark
Microsoft

Re: How to find Ticket Backlog?

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Microsoft Phil_Seamark
Microsoft

Re: How to find Ticket Backlog?

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

MaadiKemko
Frequent Visitor

Re: How to find Ticket Backlog?

Hi @Phil_Seamark,

 

Thanks a lot for your time and support. Really sorry about the minimal information.

I am planning to drow a Line and clustured bar chart for backlog analysis. 

 

In the Ist post I had attahced the snapshot of the data.

Opened On         = Ticket Open Date.

Closed On           = Ticket Closed Date (If Closed On is blank means, the ticket not resolved)

New Tickets         = Total Tickets created on one particular month (Consider Jan2016)

Closed Tickets     = Total Tickets closed on same month (Consider Jan2016)

Backlogs              = Total Tictets not closed till last month (Tickets created Till Dec2015)

Total Tickets        = Backlogs + New Tickets

 

I have done some changes in the formulas which you mentioned,

 

1) New Tickets         = COUNTA('Backlogs'[Opened On]) 

2) Closed Tickets     = CALCULATE(COUNTA('Backlogs'[Closed On]),USERELATIONSHIP('Backlogs'[Closed On],'Calendar'[Date]))

3) Backlogs              = CALCULATE(

                                                   COUNTA('Backlog'[Tickets]),

                                                   FILTER(

                                                              ALL('Calendar'[Date]),

                                                              'Calendar'[Date]<MIN('Calendar'[Date]

                                                              )

                                                  )

                                 ) -

                                CALCULATE(

                                                    COUNTA('Backlog'[Tickets]),

                                                    USERELATIONSHIP('Backlog'[Closed On],'Calendar'[Date]),

                                                    FILTER(

                                                               ALL('Calendar'[Date]),

                                                              'Calendar'[Date] < MIN('Calendar'[Date])) -1)  

 

Error.PNG

 

 

 

 

 

But here I am getting an error. Can you please help me to restruture the formula and please explain the logic of the formula?

 

Thank in Advance,

Maadi.

Microsoft Phil_Seamark
Microsoft

Re: How to find Ticket Backlog?

Please try this

 

Backlogs = CALCULATE(
                     COUNTA('Backlog'[Tickets]),
                     FILTER(
                           ALL('Calendar'[Date]),
                           'Calendar'[Date]<MIN('Calendar'[Date]
                           )
                       )
                    ) - (
             	CALCULATE(
                       COUNTA('Backlog'[Tickets]) ,
                      	USERELATIONSHIP('Backlog'[Closed On],'Calendar'[Date]),
						FILTER(
								ALL('Calendar'[Date]),
								'Calendar'[Date] < MIN('Calendar'[Date])
								 )
						 )-1)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

MaadiKemko
Frequent Visitor

Re: How to find Ticket Backlog?

Hi @Phil_Seamark,

 

I have modified the formula as you mentioned,

Backlogs = CALCULATE(COUNTA('Backlog'[Tickets]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<MIN('Calendar'[Date]))) - (CALCULATE(COUNTA('Backlog'[Tickets]), USERELATIONSHIP('Backlog'[Closed On],'Calendar'[Date]), FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] < MIN('Calendar'[Date]))) -1)  

 

But after applying the formula, I am getting the below output with -ve backlog.

Issue.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

Please see the expected result. Here Backlog = Total Pending Tickets till last month (Total Tickets - Closed Tickets)

Please find the Data from here

Backlog.PNG

 

 

 

 

 

 

Maadi

MaadiKemko
Frequent Visitor

Re: How to find Ticket Backlog?

@Phil_Seamark I have shared the data in the previous message. Did you got time to check what is the issue?

Microsoft Phil_Seamark
Microsoft

Re: How to find Ticket Backlog?

Hi there. I did see that and it will be very helpful. Just enjoying a nice summer evening sunset first. 😀

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Microsoft Phil_Seamark
Microsoft

Re: How to find Ticket Backlog?

Hi @MaadiKemko,

 

I found the problem in the formula.  Is was counting the  blank records from closed date so I added an extra filter to the 2nd calculate (which subtracts closed ticktets).

 

We must be getting close 🙂

 

 

Backlogs = CALCULATE(
                     COUNTA('Backlogs'[Tickets]),
                     FILTER(
                           ALL('Calendar'[Date]),
                           'Calendar'[Date]<MIN('Calendar'[Date]
                           )
                       )
                    ) - (
             	CALCULATE(
                       COUNTA('Backlogs'[Tickets]) ,
                      	USERELATIONSHIP('Backlogs'[Closed On],'Calendar'[Date]),
			FILTER(
				ALL('Calendar'[Date]),
				'Calendar'[Date] < MIN('Calendar'[Date])
				 )
		            ,FILTER('Backlogs','Backlogs'[Closed Tickets] <> BLANK())		 )
		 )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors