Forum Discussion

Marfield's avatar
Marfield
Icon for Helper I rankHelper 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 ...
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!
FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper 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