cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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!

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors