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())		 )
		 )

So Phil_Seamark is in the Southern Hemisphere :smileyhappy: enjoying the summer :smileyhappy:

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.

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())		 )
		 )

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.

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.

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

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