Tickets, Open, Closed and backlog

[Emoes]

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.

[DiKi-I]

Hi @Ibendlin

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

[lbendlin]

@DiKi-I please open a new thread.  Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[RubenBC]

Hi All,

I have a similar situation than you @Emoes, @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

[lbendlin]

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?

[Pavani]

Great thank you 

[Emoes]

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]

"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.

[Emoes]

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

[lbendlin · Accepted solution]

Here's how I would calculate backlog.  Feel free to adjust.

[Nuray]

Hello, I have also similiar issue, I implemented exactly same steps for dashboard, however the graps is giving all tickets no matter of when it is created/closed, and backlog is not correct as well. Can you help me to figure it out? 

[Emoes]

Hello lbendlin,

Thanks for your quick reply.

This is exactly what I want, thanks for your help.

May I ask what I did wrong (to learn from my mistakes)

I Will mark this as solved.

Again a great thanks for your help.

Emoes.

[lbendlin]

- prepare good sample data that fully covers your issue

- try and find a good wording for the expected outcome - I really got confused by the "two month lookback" comment.  Ideally show the expected outcome based on the sample data you provided

- note that you can sort one column by another column, and that you can change the sort order of the visual axis after that

- read about active and inactive relationships  (needed in your case for the Close numbers)

- when writing measures, test every intermediate step, and test it often, to make sure you're still on the right track.  It is easy to get lost in context transitions.