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
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
Phil_Seamark
Employee
Employee

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!

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!

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!

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.

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!

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

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

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!

So @Phil_Seamark is in the Southern Hemisphere Smiley Happy enjoying the summer Smiley Happy

 

replying from his phone (my phone replies are also smaller font)

 

Okay NZ would GMT+13 8:00 AM Feb 22 while here it is GMT -6 1:00 PM Feb 21 right now

Hi @Sean,

 

Can you please look into this issue, since @Phil_Seamark is not availble.

Last formula also not working as expect.

 

Please find the Data from here

 

Let me know if you required any other information.

 

Thank in Advance,

Maadi.

I'm trying to solve this same problem. Was this ever resolved?

So is this one not working?

 

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!

Hi, Phil,

 

I am researching how to use PowerBI to derive Incident Created (New), Resolved and Backlog by period specified and I come across your suggestion here. I had adopted your suggestion. However, it doesn't really work for Resolved and Backlog and I hope you can help me on this.

 

Here is the result with your suggested formula.

 

Result with Suggested Formula.JPG

I did a validation Created (New), the figures tally, sreen shot below. As for Resolved, it is not, I think it has something to do with the filter or date. For Backlog, it is not something that I am looking for. I am searching for a DAX that can calculate backlog and be able to count itself as backlog across its lifecycle until the end of it (Resolved). For example, a ticket A123456 created on 5 November 2016 and resolved on 16 April 2017, when I plot or tabulate Backlog by period, let say by month, the expected result is this ticket should count itself a backlog in November 2016, December, Januray, February and March 2017.

 

Expected Result.JPG

Let me paste/send over the data set for your reference. Hope you could shed some light here. Data set as follow

https://1drv.ms/u/s!AiwAA0c-XzPHgSSRl53z-_elVLtS

 

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
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.