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.
Hi andrew_hardwick, I have solved the issue. it was related to the data type using on the dates fileds in my model. Since I use a master calendar (follow your suggestion) to link with my "tickets" table, and my tickets table has 2 dates fields (Open Date and Close date) I found a situation related to the "close date" field. Some of the tickets are Cancelled, therefore there is no close date in the table, so I have to create a calculate field using the State of each record to figure out if the ticket was cancelled, if so, I'm using Updated date isntead. But, what this cause is that for some reason, I have to change the data type for all the dates field in order to match them as Date (taking away the time portion). At the end and having the same data type for all the date field involve on this the model is working. Hope the explanation can't cause more confution!!
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])))))
