Cumulative backlog to date

[andrew_hardwick]

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

[slapdragon]

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.

[lucasvaliente]

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?

[lucasvaliente]

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?

[lucasvaliente]

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?

[Sean]

@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!

[Sean]

@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!

[andrew_hardwick]

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

[Sean]

@andrew_hardwick 

Here's a link on how to create a Calendar table

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

Then just set up the relationships and create the measures as in my picture above.

Hope this helps. Let me know if you have any other questions.

[andrew_hardwick]

@Sean

Thats great. thanks for that, I now have a Calendar. I have built my report using the measures you stated and the Calendar and it is now almost there, I have the balance and RT Balance showing. The ony remaining issue I see is that some of the lines do not yet have a resolved date, they are still being worked on. This therefore gives me a negative RT Balance at the start as their is no date assigned. Instead I would like this open entries to appear on the latest RT Balance day, i.e. today.

Any idea how I could achieve this?

[Sean · Accepted solution]

@andrew_hardwick Couple small changes since there are blank dates...  But you should be set now! Let me know.

[asah]

Hi @Sean & @andrew_hardwick ,

I have a similar requirement to produce a running total backlog at the end of each week and I think the key is to get the values for each day as illustrated in this example. In my model, a table with dates and other Org specific values already exist and the Date (Ticket Created) field in the master table is linked to the Date field in the Dates table. However, I am having an issue with linking both Date (Ticket Created) and Date (Ticket Solved) to the Date field in the Date table as the relationship for Date (Ticket Solved) remains inactive. I get the message 'You can't create a direct active relationship between Table A and Date Table because an active set of indirect relationships already exists between these tables. To make this relationship active, set the Cross filter direction to "single", delete or deactivate any of the indirect relationships first.".

Once I get past this, I need some help in segregating the backlog of tickets based on ticket type which can be one of "incident", "problem", "question", "task" and by priority which will be one of "urgent", "high", "normal", "low". All tickets being counted as backlog should be out of SLA for which I am using the SLA metric assigned by the tooling solution and have created a field IsSLABreach = True / False. So for my requirement, IsSLABreach should always be true.

Kindly help and oblige.

[Anonymous]

Hey @Sean , thanks a lot! I found this very helpful. I know the post is kind of old, but it's gold.

And I also have a question, I needed the RT Balance, and I also need to find the closed balance from previous periods. For example, this month I resolved three tickets that where created on previous months, I need to show that in the chart along the RT Balance, how would I write a measure for that? I have tried several things, but nothing seems to be working.

Any advice or guidance in the right direction would be appreciated. Thanks a lot!

[Anonymous]

Hello all,

The calculation for the backlog worked perfectly. However, I would like to display the sum of "points" assigned to each ticket instead of the count of the opened tickets, closed tickets, and RT balance.

Ticket Number     Opened Date     Closed Date     Points

1234                     01-01-2019        02-01-2019       7

1254                     01-02-2019        01-09-2019       3

5432                     01-31-2019        02-01-2019       1

1456                     01-22-2019        02-08-2019       4

So I would like to display the sum of points for the opened, sum of points for the closed, and sum of points for the backlog.

Thank you in advance!

[diegoadum]

Hi guys, using the opened date from the tickets table everything looks awesome, but using the dates from the date table I got this behavior about to get the negative values at the beginning of the table. Any suggestion?

[andrew_hardwick]

@diegoadum I'm not entirely sure what your issue is, can you elaborate and I will see if I can help?

[diegoadum]

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!!

[medwards807]

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.

[andrew_hardwick]

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?

[medwards807]

@andrew_hardwick

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])))))

 

[Romain24154]

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