Forum Discussion
Cumulative backlog to date
I am trying to create a line graph in Power BI to show the difference between a number of opened tickets vs resolved tickets on a daily basis. I then want to show a line graph of the cumulative data.
I am new to Power BI and whilst I can easily achieve this in Excel, I cannot get what I need. The data is shown below from Excel. The Balance is the difference between Opened and Resolved and then each day it either increases or decreased based on this difference.
| Date | Opened | Resolved | Balance |
| 18/04/2016 | 52 | 35 | 17 |
| 19/04/2016 | 62 | 47 | 32 |
| 20/04/2016 | 111 | 91 | 52 |
| 21/04/2016 | 130 | 103 | 79 |
| 22/04/2016 | 141 | 96 | 124 |
| 23/04/2016 | 6 | 0 | 130 |
| 24/04/2016 | 8 | 1 | 137 |
| 25/04/2016 | 108 | 80 | 165 |
| 26/04/2016 | 101 | 88 | 178 |
| 27/04/2016 | 73 | 71 | 180 |
| 28/04/2016 | 69 | 55 | 194 |
| 29/04/2016 | 93 | 100 | 187 |
| 30/04/2016 | 4 | 0 | 191 |
| 01/05/2016 | 2 | 0 | 193 |
| 02/05/2016 | 3 | 1 | 195 |
| 03/05/2016 | 85 | 97 | 183 |
| 04/05/2016 | 68 | 98 | 153 |
| 05/05/2016 | 60 | 75 | 138 |
| 06/05/2016 | 73 | 80 | 131 |
| 07/05/2016 | 3 | 0 | 134 |
The raw data I have is as below, where the Incident Number is a unique reference
andrew_hardwick Couple small changes since there are blank dates... But you should be set now! Let me know.
21 Replies
andrew_hardwick Can you post what results you expect for the data sample you've provided on the bottom?
Opened Resolved Balance
April 8, 2016 1 0 1
April 14, 2016 3 0 4
April 15, 2016 1 0 5
April 18, 2016 7 8 4
April 19, 2016 0 2 2
May 4, 2016 0 1 1
May 6, 2016 0 1 0
Also do you have a Calendar Table - you will need one!
andrew_hardwick You will need a Calendar Table for this one.
Relate both Opened and Resolved Dates from your table to Date in the Calendar Table
Then the Measures are pretty straightforward. Let me know if you have any questions!
Hi Sean
No, I do not have a Calendar Table, so I suspect this is where I am going wrong. Do you know where I find one, or how to create one?
PS, I really am new to this :-)
Thanks,
Andy
- good afternoon, okay? I would like to take a doubt that is making me sleepy already! I discovered the power bi and am madly in love with this software that in my opinion will dominate the world! Where is my problem ??? I'm breaking my head like a madman in order to calculate the backlog in the tool! for example I have projects started from January to now and I need to know what was left open without negotiation. Can you help me please?
- good afternoon, okay? I would like to take a doubt that is making me sleepy already! I discovered the power bi and am madly in love with this software that in my opinion will dominate the world! Where is my problem ??? I'm breaking my head like a madman in order to calculate the backlog in the tool! for example I have projects started from January to now and I need to know what was left open without negotiation. Can you help me please?
good afternoon, okay? I would like to take a doubt that is making me sleepy already! I discovered the power bi and am madly in love with this software that in my opinion will dominate the world! Where is my problem ??? I'm breaking my head like a madman in order to calculate the backlog in the tool! for example I have projects started from January to now and I need to know what was left open without negotiation. Can you help me please?
I would like some help here as well please. I have followed the steps and managed to get the numbers working and used a line graph.
The issue I am facing is that the numbers tend to continue for the future weeks though there is (obviously) no data for those dates.
In the image, the Week24 is from 10/08/2020 till 16/08/2020. I don't have any data past those dates. But the backlogs for the NEXT week, Week25, shows as 12 (I've got no idea where that's coming from). And that same value goes on all the way till the end of the year, which is Week53.
Below are the relationships and measures that I have used:
Opened = COUNTA('Main'[Date (Ticket Created)])
Resolved = CALCULATE(COUNTA('Main'[Date (Ticket Solved)]), USERELATIONSHIP(Week[Dates], 'Main'[Date (Ticket Solved)]))
RT Balance = CALCULATE([Balance], FILTER(ALL(Week), Week[Dates] <= MAX('Main'[Date (Ticket Solved)])))
Backlog = CALCULATE('Main'[RT Balance], Week[Year]="2020-21")Any help with this would be great. Thanks.
