Forum Discussion
Running Sum in Power BI for Ticket Backlog
I have IT Tickets data with below shown columns
- Ticket Number
- Ticket Status
- Ticket CreatedDate
- Ticket ResolvedDate
I would like to create a report like below...
| Month | Created Count | Resolved Count | Backlog | Calculation |
| 100 | <- Backlog | |||
| Jan-19 | 50 | 40 | 110 | <- 100+50-40 |
| Feb-19 | 100 | 80 | 130 | <- 110+100-80 |
| Mar-19 | 200 | 100 | 230 |
I have the raw data and PBIX in one drive for you review.
https://1drv.ms/u/s!AuZ8zsEu-lf-aKZZdq9ll8lX0JA?e=Z93Vf4
Problem is I have done this in Tableau and QlikView in just a right click on mouse. But I have no clue on how to achieve these three metrics in a single table in Power BI. I have the similar requirement to show the same table by IT Team list instead of Month list etc.,
You help is greatly apreciated
Hi!
I think you could make a calendar table and establish relationships between the calendar table date and the creation and resolution dates. You can then use these relationships to compute the measures you need.
Here's what I've done.
Created a calendar table called DimDate using CALENDARAUTO()
Created relationships between the Date variable from the Calendar table and the Creation and Resolution Date.
I then created the 3 measures:
Created Count = CALCULATE(COUNT(data[ID]))
Resolved Count = CALCULATE(COUNT(data[ID]);USERELATIONSHIP(data[Resolution Date];DimDate[Date]);data[Resolution Date]<>BLANK())
Backlog = CALCULATE([Created Count]-[Resolved Count];FILTER(ALLSELECTED(DimDate);DimDate[Date]<=max(DimDate[Date])))
Created, Resolved, Backlog
I hope this works for you!
11 Replies
Hi!
I think you could make a calendar table and establish relationships between the calendar table date and the creation and resolution dates. You can then use these relationships to compute the measures you need.
Here's what I've done.
Created a calendar table called DimDate using CALENDARAUTO()
Created relationships between the Date variable from the Calendar table and the Creation and Resolution Date.
I then created the 3 measures:
Created Count = CALCULATE(COUNT(data[ID]))
Resolved Count = CALCULATE(COUNT(data[ID]);USERELATIONSHIP(data[Resolution Date];DimDate[Date]);data[Resolution Date]<>BLANK())
Backlog = CALCULATE([Created Count]-[Resolved Count];FILTER(ALLSELECTED(DimDate);DimDate[Date]<=max(DimDate[Date])))
Created, Resolved, Backlog
I hope this works for you!
Thanks for replying. I tried the steps but not able to replicate the same chart as you did..
Can you help me with your PBIX..?
I can also see Creation Date and Resolution Date columns in your screenshot.. But I dont have those.. AM i facing the issue because of this..?
I created relationship like DimDate Date = Date Created (Many to Many and Single)... I did anything wrong?
Hi!
Once you have the time table, you just have to create the relationship between the Date of the time table and the creation date and resolution date. Just make sure all dates are in the same format (I've set them to dd/MM/yyyy). Since you're interested in the date without hours and minutes, you can also extract them in a new column, like so:
Creation Date = data[Created].[Date]
Resolution Date = data[Resolved].[Date]
You then establish the relationships as shown in the screen below:
First you drag and drop the Date field from the DimDate table to the Creation Date field. This creates an active 1 to Many relationship between the Date and the Creation Date.
Then you drag and drop the Date field from the DimDate table to the Resolution Date field. This creates an inactive 1 to Many relationship between the Date and the Resolution Date. This is the relationship that we activate when using the USERELATIONSHIP function in the measure for the Resolved Count.
Relationships between ddates
Then your results will certainly be correct!
Thanks, this is great!
Hello Anonymous !
Thanks for reaching out! I've tried to replicate your steps but haven't been able to replicate the issue. It works fine on my side. I'm attaching pbix and excel file:
Let me know if this helps! Otherwise I'll be happy to review a demo of your data to see if I can help further!
Have a great weekend,
Carlo
Hi carlomoretto ,
Many thanks for your reply and for taking the time to look at this.
I've reviewed your file and I can confirm that it is indeed working as expected. I've also double-checked all measures and relationships with my file and everything seems to be ok, but I was still getting the volumes assigned to a blank date.
Then, i realized that i had another created and resolved column, with the same data in it. I changed the relationship to those columns and the measures and voilá, it worked.
Thanks a lot for your help!
Hi carlomoretto , community,
I have a very similar issue than this one that i've tried to resolve without the desired outcome.
My scenario is very similar to this one. I have a volume of incidents created in a specific date and the resolution date for each incident.
I've created the calendar and relationships above and they seem to be fine:
I've also ensured that the calendar, created and resolved date have the same format and created the 3 measures as per below:
*Created Count = CALCULATE(COUNT(Issues[Issue ID]))*Resolved Count = CALCULATE(COUNT(Issues[Issue ID]),USERELATIONSHIP(Issues[Resolved], 'Calendar'[Date]),Issues[Resolved] <> BLANK())*Backlog = CALCULATE([*Created Count]-[*Resolved Count], FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date] <=MAX('Calendar'[Date])))But when i create the table/matrix, it puts the values on a blank date, not under the correct date that should be October and November 2020:Can someone help me with this topic? What am i doing wrong?
Thanks!
