Forum Discussion
Tickets, Open, Closed and backlog
Here's how I would calculate backlog. Feel free to adjust.
Super User"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.
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
Here's how I would calculate backlog. Feel free to adjust.
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.
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?