Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Calculate Backlog on a past date

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.

 

 

3 REPLIES 3
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
TicketIDCreatedDateResolutionDateResolved
11/1/20171/3/2017yes
21/1/20171/4/2017yes
31/1/2017NULLno
41/2/20171/2/2017yes
51/2/20171/4/2017yes
61/2/20171/4/2017yes
71/3/2017NULLno
81/3/20171/5/2017yes
91/4/2017NULLno
101/5/2017NULLno

 

 

Date
DateYearMonthWeek……. 
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     

 

 

DateIncomingResolvedBacklog that DayTotal Backlog
1/1/20173033
1/2/20173125
1/3/20172116
1/4/201712-15
1/5/20171105
1/6/2017    
1/7/2017    

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors