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.
The raw data I have is as below, where the Incident Number is a unique reference
Solved! Go to Solution.
@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!
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 🙂
Here's a link on how to create a Calendar table
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.
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?
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?
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.