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...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello lbendlin

       

      You are right, I made a mistake in generating the test data.

      The opened (created) date could not be before the closing date.

      I created a new file and loaded this into Power BI, including the Date table I have generated.

       

      This file can be found here! (Dropbox)

       

      I used your code from above to generate the Backlog (in the Date Table)

       

      As you can see in the view I can’t get it right to show.

      • The Sort order of the months starting from January 2021 till December 2022 is not correct
        • First, you see march 2021, followed by January 2021 and even 2022 is in the middle
      • I would expect to see the backlog in February 2022 that is 138 (Blank “Date Closed”

       

      I think it is almost there, but I can’t get it right 🙂

       

      Thanks for your help.

      Emoes