Calculate Backlog on a past date

[Anonymous]

I am using my on-premise JIRA db from which I have pulled all tickets data since Jan 1 , 2016 and creating some reports and dashboards.

I have a Tickets table having :

Ticket#

CreatedDate

ResolutionDate

ResolutionStatus (this is NULL incase the issue is not yet resolved or else it stores somes numbers telling the status type.)

I have a Date table also.

Date [Date] is having a primary relation with CreatedDate and a secondary with ResolutionDate.

I am able to fetch the count of below across any time period (any day/week/month etc) :-

incoming -  (Count[Tickets])

resolved  - (CALCULATE( COUNTROWS(Tickets) , USERELATIONSHIP( Tickets[ResolutionDate], 'Date'[Date])))

Now I also want to fetch data for Total Backlog across any period of time.

Just to explain what I need is :-

Incoming on Jan 1 2017 = Tickets Created Date on Jan 1
Resolved on Jan 1 2017  = Tickets Resolution Date on Jan 1

Total Backlog on Jan 1 2017 =
(Tickets Created on and before Jan 1 which were Resolved on and after Jan 1) + (Tickets Created on and before Jan 1 which are still unresolved) 

or else 

Total Backlog on Jan 1 2017 = Total Backlog on Dec 31 2016  +  (Delta of Created and Resolved  on Jan 1 2017)

Please suggest how I can achieve this. 

I will repeat that my data is from Jan 1 2016 and I queried the database to find that on Jan 1 2016 the backlog was 258.

[v-yulgu-msft]

Hi @Anonymous,

I am not clear about your desired result. Would you please provide some detailed sample data and if possible show us your required output with an image?

Maybe you could try below measures:

Incoming on Jan 1 2017=CALCULATE( COUNTROWS(Tickets) , FILTER(Tickets,Tickets[CreatedDate]=DATE(2017,1,1))

Resolved on Jan 1 2017=CALCULATE( COUNTROWS(Tickets) , FILTER(Tickets,Tickets[ResolvedDate]=DATE(2017,1,1))

Total Backlog on Jan 1 2017=CALCULATE( COUNTROWS(Tickets) , FILTER(Tickets,Tickets[CreatedDate<=DATE(2017,1,1))

Best regards,
Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft / Others,

Below is a sample data with the last table showing the desired result.

Incoming is  - CountA(TicketID)

Resolved is  -  CALCULATE( COUNTA(Tickets[ResolutionDate]) , USERELATIONSHIP( Tickets[ResolutionDate], 'Date'[Date]))

BacklogThatDay is    -   Incoming - Resolved

Total backlog is  something I need to calculate. Also keep in mind that the difference between this data and my rela data is that on the first day i.e. 1/1/2017 I do have some backlog from my past which I will need to be the start value for my Total Backlog.

I will also need Total backlog for a week/month and that should be the Total Backlog on the last day of that week/month.

Tickets Table
TicketID	CreatedDate	ResolutionDate	Resolved
1	1/1/2017	1/3/2017	yes
2	1/1/2017	1/4/2017	yes
3	1/1/2017	NULL	no
4	1/2/2017	1/2/2017	yes
5	1/2/2017	1/4/2017	yes
6	1/2/2017	1/4/2017	yes
7	1/3/2017	NULL	no
8	1/3/2017	1/5/2017	yes
9	1/4/2017	NULL	no
10	1/5/2017	NULL	no

Date
Date	Year	Month	Week	…….
1/1/2017
1/2/2017
1/3/2017
1/4/2017
1/5/2017
1/6/2017
1/7/2017
1/8/2017
1/9/2017
1/10/2017

Date	Incoming	Resolved	Backlog that Day	Total Backlog
1/1/2017	3	0	3	3
1/2/2017	3	1	2	5
1/3/2017	2	1	1	6
1/4/2017	1	2	-1	5
1/5/2017	1	1	0	5
1/6/2017
1/7/2017

[SeanM77]

I'm working on the exact samething.  Did you ever find an answer?