Ticket backlog

[Anonymous]

Dear Community,

I have a very similar issue than this one that i've tried to resolve without the desired outcome. 

My scenario is very similar to the one described in this post:
https://community.powerbi.com/t5/Desktop/Running-Sum-in-Power-BI-for-Ticket-Backlog/m-p/727052#M3508...

I have a volume of incidents created on a specific date and the resolution date for each incident. 

I've created the calendar and relationships above and they seem to be fine:

I've also ensured that the calendar, created, and resolved date have the same format and created the 3 measures as per below:

*Created Count = CALCULATE(COUNT(Issues[Issue ID]))

*Resolved Count = CALCULATE(COUNT(Issues[Issue ID]),USERELATIONSHIP(Issues[Resolved], 'Calendar'[Date]),Issues[Resolved] <> BLANK())

*Backlog = CALCULATE([*Created Count]-[*Resolved Count], FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date] <=MAX('Calendar'[Date])))

But when I create the table/matrix, it puts the values on a blank date, not under the correct date that should be October and November 2020:

Can someone help me with this topic? What am i doing wrong?

Thanks!

[Tim_H]

@v-deddai1-msft 

is it normal that when tickets are resolved they also are removed from the backlog ?   In other words as long tickets are not resolved thay are vissible but when resolved also the history is removed.

for example :

ticket opened 12/02/2019

ticket resolved 14/09/2019

when you measure in october this ticket should be visible as backlog from march until September.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

What's the data type of your [Created] and [Resolved] column? Are they Date/time? Based on my test, if your link date/time column in fact table with date column in calendar table,it will show blank column:

But after I create a new date column by [Create] and link it to the calendar table, it works well.

_CreateDate = data[Created].[Date] 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft ,

Many thanks for your help. Indeed after posting here, i continued to troubleshoot and also with help of this community i was able to find that i had to use another date column and it worked like a charm.

Thanks everyone for your help! 

[sagar512]

Hi. Can you please help me with the solution that helped you?. I need to build the backlog measure as you described.

[MFelix]

Hi @Anonymous ,

Looking at the information you provide everything should work properly, only question I have is what happens to your matrix if you only have one of the measures?

Try debugging you data one step at a time:

• Add the columns ISSUEDID to your matrix and do a count that will make sure your relationship is working propely
• Add the Created measure and check if the result still checks
• Add the Resolved measure
• Finally add the last one.

Can you share some sample file?