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 R...
Need Help
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?