cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrew_hardwick Helper II
Helper II

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.

 

 

DateOpenedResolvedBalance
18/04/2016523517
19/04/2016624732
20/04/20161119152
21/04/201613010379
22/04/201614196124
23/04/201660130
24/04/201681137
25/04/201610880165
26/04/201610188178
27/04/20167371180
28/04/20166955194
29/04/201693100187
30/04/201640191
01/05/201620193
02/05/201631195
03/05/20168597183
04/05/20166898153
05/05/20166075138
06/05/20167380131
07/05/201630134

 

Capture.PNG

The raw data I have is as below, where the Incident Number is a unique reference

 

Capture 2.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Sean Community Champion
Community Champion

Re: Cumulative backlog to date

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

 

Running Total Balance - L&C Chart2.png

View solution in original post

20 REPLIES 20
Sean Community Champion
Community Champion

Re: Cumulative backlog to date

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

Highlighted
Sean Community Champion
Community Champion

Re: Cumulative backlog to date

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

Running Total Balance - L&C Chart.png

andrew_hardwick Helper II
Helper II

Re: Cumulative backlog to date

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 Community Champion
Community Champion

Re: Cumulative backlog to date

@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 Helper II
Helper II

Re: Cumulative backlog to date

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

 

Capture3.PNG

Sean Community Champion
Community Champion

Re: Cumulative backlog to date

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

 

Running Total Balance - L&C Chart2.png

View solution in original post

andrew_hardwick Helper II
Helper II

Re: Cumulative backlog to date

Thats great Sean, thanks for your help 🙂

diegoadum Helper I
Helper I

Re: Cumulative backlog to date

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 Helper II
Helper II

Re: Cumulative backlog to date

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors