Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Ticket backlog

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#M350843

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!

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

     

5 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        sagar512
        Helper I
        5 years ago

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

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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?

  • Tim_H's avatar
    Tim_H
    Helper I
    5 years ago

    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.