Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.

 

 

Need Help

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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    
      • SeanM77's avatar
        SeanM77
        Icon for Advocate I rankAdvocate I
        6 years ago

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