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!

carlomoretto's avatar
carlomoretto
Frequent Visitor
5 years ago

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:

 

https://wetransfer.com/downloads/311df5465449a6e1cb37fe060ba818d520201114103046/4666a444d634f3d3227b47b6f58d1d3b20201114103206/44e34e 

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!