Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Tickets, Open, Closed and backlog

Hello All,

 

I’m new to Power Bi and thought I knew enough from Excel to make it in Power Bi….

Not.

 

Attached (via Dropbox, because I could not add attachments directly to this post) I have the example in Excel (Test data)

 Backlog test data 

I have the following:

I have a list of Servicedesk tickets.

Those tickets have a creation date, and if they are closed a Closed date.

 

I would like to create an overview showing one or more years but per month (with a chart) of the Tickets created per month, the tickets closed in that same month and also the Backlog per month.

 

In the attached excel you will find the Created date (time is not important), de Date Closed.

 

And a Column containing the last day of the month, and behind that, calculated the backlog (the number of open items in that specific month)

 

=COUNTIFS(A:A;"<="&EOMONTH(A2;0);B:B;">"&EOMONTH(D2;0))+COUNTIFS(A:A;"<="&EOMONTH(D2;0);B:B;"")

 

 

Is someone able to help me out to create this overview in Power Bi?

I also have a date table available in Power Bi.

 

Thanks.

Need Help

12 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    See attached for one implementation of the tickets opened/closed per month.  Read about USERELATIONSHIP.

     

    For the backlog you need to provide more information. What's the beginning backlog?  How do you define current backlog?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello Ibendlin,

       

      Thank you for your reply and “Ticketpbix” file.

      The “attached Excel file” is calculating the backlog, with the following formula:

      =COUNTIFS(A:A;"<="&EOMONTH(A2;0);B:B;">"&EOMONTH(D2;0))+COUNTIFS(A:A;"<="&EOMONTH(D2;0);B:B;"")

       

      By validating if a ticket for that month or the previous month was still open at the end of the month.

      I don’t have to provide a “start” backlog date. Excel is calculating this for me.

      I was hoping to use the same method in PowerBi  so that based on a Creation date and having or missing a close date it could calculate the open items at the end of each month.

       

      Thanks for your help already.

      Emoes

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        "validating if a ticket for that month or the previous month was still open at the end of the month." 

         

        that would be equivalent to 

         

         

        Backlog = 
var em = ENDOFMONTH(Dates[Date])
var f = filter(all(Tickets),Tickets[Created]<=em && Tickets[Created]>=edate(em,-2) && COALESCE(Tickets[Closed],em)>em)
return countrows(f)

         

         

        but it does not match your backlog table at all.  Please explain your business logic for calculating backlog.

        Please explain why some tickets are closed before they are created.

         

         

  • RubenBC's avatar
    RubenBC
    Frequent Visitor
    4 years ago

    Hi All,

     

    I have a similar situation than you Anonymous, lbendlin  . Following your example and the solution, if I would like a list of tickets in backlog, do you know how I can do it? I mean, I select one point in the graph the filter only apply to the month I select and it doesn't show the backlog data from previous months

     

    For example If I select July 2021, the table shows the tickets open on July and not the 66 backlog list.

     

     Thanks

  • DiKi-I's avatar
    DiKi-I
    Post Partisan
    2 years ago

    Hi @Ibendlin

    How I can get this work both at daily,montly and yearly grain?