Forum Discussion

Marfield's avatar
Marfield
Helper I
1 year ago
Solved

Backlog average age by day

Hi,

 

I need to calculate the average age of active tickets relatively to each day in my calendar table on the X axis.

The way it is calculated is by adding +1 for each day where the ticket isn't solved (between start and end dates) and where the status is 0. Then the average is calculated on active tickets by date.

 

Below an example of what I am trying to achieve :

 

 

My datamodel is as follow :

 

Thanks for the help.

AVERAGE PER DAY
Close and Backlog Tickets
DATEDIFF
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    Done

     

    C:\Users\Io\Il mio Drive\Cartelle Condivise Support Microsoft\Avg Age

     

    here the result

    but you need to identify each ticket uniquely and label it in another column as the same ticket, here the dimension i created, you find everything in the pbix

     

     

    The measure (it was tough, therefore THANK YOU, so I do not get bored with easy stuff 🙂 )

     

    Nr Days open Tickets =
    VAR MaxDate =
        MAX ( 'Calendar'[Date] )
    VAR NrOfTickets =
        CALCULATE (
            DISTINCTCOUNT ( Tickets[TicketName] ),
            Tickets[StartDate] <= MaxDate,
            Tickets[Status] = 0,
            (
                Tickets[EndDate] > MaxDate
                    || ISBLANK ( Tickets[EndDate] )
            ),
            REMOVEFILTERS ( 'Calendar'[Date] )
        )
    RETURN
        DIVIDE (
            SUMX (
                VALUES ( 'Calendar'[Date] ),
                SUMX (
                    'Tickets dim',
                    VAR StartDate =
                        CALCULATE (
                            MIN ( Tickets[StartDate] ),
                            REMOVEFILTERS ( 'Calendar'[Date] ),
                            Tickets[Status] = 0
                        )
                    VAR EndDate =
                        CALCULATE (
                            MIN ( Tickets[EndDate] ),
                            REMOVEFILTERS ( 'Calendar'[Date] ),
                            Tickets[Status] = 0
                        )
                    RETURN
                        IF (
                            'Tickets dim'[Status] = 0
                                && MaxDate >= StartDate
                                && (
                                    ISBLANK ( EndDate )
                                        || MaxDate < EndDate
                                ),
                            INT ( MaxDate - StartDate ) + 1
                        )
                )
            ),
            NrOfTickets
        )
     
    If it is ok please give kudos and mark it as a solution
     
    Thanks!

14 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    Can you provide a sample data for the Tickets table please? Data for 3 or 4 tickets will do, some with end date some without so I can reproduce and solve

     

    best

    • Marfield's avatar
      Marfield
      Helper I
      1 year ago

      You may find the file on dropbox here

      Thanks for your time

      • FBergamaschi's avatar
        FBergamaschi
        Super User
        1 year ago

        If you provide me a mail I can send you my pbix with my code working

  • m4ni's avatar
    m4ni
    Resolver I
    1 year ago

    Hi Marfield 

    Not knowing too much about your model, but you can try this measure.  It should give some idea at least...

     

    Measure =
    VAR Val1 = SUM('Table'[YourValue])
    VAR Val2 = DISTINCTCOUNTNOBLANK('Table'[YourValue])
    RETURN DIVIDE(val1, Val2,0)
     
    I have mocked something based on your screenshots and have the below result...
     

     

    HTH

     

    • Marfield's avatar
      Marfield
      Helper I
      1 year ago

      I don't have column values to calculate, only the name of the ticket, the startDate and the endDate.
      I tried your code with a calculated column with the value 1, I got this :


      There is a link to a sample model in one of my comments if you like.

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    Nr Days open Tickets =
    VAR MaxDate =
    MAX ( 'Calendar'[Date] )
    VAR MainStartDate =
    CALCULATE ( MAX ( Tickets[StartDate] ), REMOVEFILTERS ( 'Calendar' ) )
    VAR NrOfTickets =
    CALCULATE (
    COUNTROWS ( Tickets ),
    Tickets[StartDate] <= MaxDate
    && Tickets[EndDate] > MaxDate,
    REMOVEFILTERS ( 'Calendar'[Date] )
    )
    RETURN
    DIVIDE (
    SUMX (
    VALUES ( 'Calendar'[Date] ),
    SUMX (
    VALUES ( 'Tickets dim'[TicketName] ),
    VAR StartDate =
    CALCULATE (
    MIN ( Tickets[StartDate] ),
    REMOVEFILTERS ( 'Calendar'[Date] ),
    Tickets[Status] = 0
    )
    VAR EndDate =
    CALCULATE (
    MIN ( Tickets[EndDate] ),
    REMOVEFILTERS ( 'Calendar'[Date] ),
    Tickets[Status] = 0,
    NOT ISBLANK ( Tickets[EndDate] )
    )
    RETURN
    IF (
    MaxDate < EndDate
    && MaxDate >= StartDate,
    INT ( MaxDate - StartDate ) + 1
    )
    )
    ),
    NrOfTickets
    )

     

    If it works, please give kudos and/or marka as a solution

     

    Thanks

    • Marfield's avatar
      Marfield
      Helper I
      1 year ago

      This isn't exactly what I'm looking for unfortunately, I need to calculate each day the average age of the tickets.

      On the second date on the axis, the ticket A is 2 days old and the ticket B is 1 day old, therefore, 3 days in total. I divide that by by the number of active tickets (2), so the average is 1.5

      • m4ni's avatar
        m4ni
        Resolver I
        1 year ago

        Marfield 

        I assume my small fix would not be correct either based on your requirements but please share if its feasible or working to some extent.  The measure returns the correct totals as per your eample.

         

        Please update.