Forum Discussion
Cumulative backlog to date
andrew_hardwick Couple small changes since there are blank dates... But you should be set now! Let me know.
I am working on Something very similar and following the details of this post. I am reviewing the details and can't seem to get the measures listed to work.
My sample data is as follows:
I have open tickets and resolved tickets working just fine on my Bar Chart, just can't get the Backlog Line to work.
The logic is I have to work backwards as follows:
I start with the current Active Cases (which is the 241 July-17 Backlog, then I add in the Resolved and subtract out the new. i.e.
June 17 Backog = 241 + 19 - 26
I have the Dates Tabe and # Created and # Resolved Measures working, could you share detail on how you calulated your Backlog ?
| Opened | Resolved | Backlog | |
| Apr-17 | 34 | 24 | 212 |
| May-17 | 38 | 36 | 214 |
| Jun-17 | 40 | 20 | 234 |
| Jul-17 | 26 | 19 | 241 |
This is the end result based on the sample data.
Hi medwards807
OK I have opened and resolved, which are both measures.
Then I have two calculations. The first is a measure called balance.
Balance = [Opened]-[Resolved]
I then use another measure called RT Balance (Running Total). This is the one that I use on my graph.
RT Balance = CALCULATE([Balance],FILTER(ALL('Date'),'Date'[Date]<=max('Date'[Date])&&NOT(ISBLANK('Date'[Date]))))
This looks like this-
Hope that helps?
Hi andrew_hardwick,
I was reading through this post and tried oud your solution without success.
I think my situation might be slightly different and hoped you could help on this one to :
I prepared a simplified version of my raw data (here below)
So basically, i have a ticket on each line.
Each ticket has a opened date, a target for resolution and an actual resolution date
At a given date (today in below example) a ticket is either resolved, late (backlog) or opened and still within time window allowed for resolution.
A ticket may have been late (backlog) at a certain time
What i'm trying to achieve:
I would like to set up a cumulative backlog that will show overtime the cumulated backlog and be able to split the line between site A and site B
Thanks for your help and your time !
Source ticket ID Date opened Target closure date closed date is backlog is or has been backlog Site A 1 01/01/2016 16/01/2017 15/01/2017 Site B 2 01/01/2016 16/01/2017 18/02/2017 yes yes Site B 3 01/01/2017 16/01/2017 18/01/2017 yes Site A 4 01/02/2017 16/02/2017 15/02/2017 Site A 5 01/02/2017 16/02/2017 17/02/2017 yes Site B 6 01/05/2017 16/05/2017 16/06/2017 yes yes Site B 7 01/05/2017 16/05/2017 15/05/2017 Site A 8 01/07/2017 16/07/2017 yes yes Site A 9 01/07/2017 16/07/2017 yes yes Site B 10 01/07/2017 16/07/2017 yes yes Site A 9 15/07/2017 30/07/2017 Site B 10 15/07/2017 30/07/2017 This solution works great, thanks alot for the help, I do have just one additional slight bug that I am trying to figure out as follows: I have 2 months where the Backlog is off by 1, is Oct 16 and Dec 16, it looks to be that what is different on these 2 months are the # Created is blank so the backlog seems to bot have recognized that until it hits the next day with a # created value. All of the other period's are correct.
My Measures:
# Created = CALCULATE(COUNTROWS(Incidents),USERELATIONSHIP(Incidents[Created Date],Dates[Date]),incidents[Status] <> "Cancelled")
# Resolved = CALCULATE(COUNTROWS(Incidents),USERELATIONSHIP(Incidents[Resolved Date],Dates[Date]),incidents[Status] <> "Cancelled")
Balance = CALCULATE(incidents[# Created] - incidents[# Resolved])
Backlog =
CALCULATE(incidents[Balance],FILTER(ALL('Dates'),and('Dates'[Date]<=max('Dates'[Date]), NOT(ISBLANK('Dates'[Date])))))
