Forum Discussion
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...
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
Advocate I
Advocate II'm working on the exact samething. Did you ever find an answer?