Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 Re...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • carlomoretto's avatar
    carlomoretto
    7 years ago

    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!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks a lot. Now i followed the instructions and able to replicate the same visual..

 

All  calculation seems to be fine except for January (Backlog 224 + Created 6644 - Resolved 5639) so Jan backlog is 1229. It is showing 781.

 

Also if I have a Support Team column (L1 Support, L2 Support) in the data. And if I need a visual like below.. Is it possible?

 

 JanuaryFeb
Support GroupOpened CountResolved CountBacklogOpened CountResolved CountBacklog
L1 Support10050501005050
L2 Support1232310012323100
carlomoretto's avatar
carlomoretto
Frequent Visitor
7 years ago

Sorry, my mistake, I forgot to exclude the blank dates from the Resolved Count measure:

 

Resolved Count = CALCULATE(COUNT(data[ID]);USERELATIONSHIP(data[Resolution Date];DimDate[Date]);data[Resolution Date]<>BLANK())
 
That should do
 
You can get the table you're looking for by simply creating a Matrix visual, where you will put the Date on the Columns, the Groups on the rows and the three measures on the values.
 
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you so much.. YOU are awesome....

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    carlomoretto  My friend the solution is working perfectly. One more help.

     

    In our solution we were using the data fields as DATE ONLY. If I change it to DateTime then the results are not coming up. Is there any ways to use the date fields as DateTime with the same solution?

     

    Reason is I have to bring a count like below...

                       Opened      Closed    Backlog (Carry to next shift)

    Shift 1        100              20              80

    Shift 2         80+50       60              70

    Shift 3         70+10       10              70

     

    Where Shift 1 is 6 am to 2 pm, Shift 2 is 2pm to 10 PM and Shift 3 is 10 PM to 6 AM

    80+50 and 70+10 means - 80 came from Shift 1 and 50 new tickets got created in shift 2.

     

    You idea will help me a lot....