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!
Marfield's avatar
Marfield
Icon for Helper I rankHelper I
1 year ago

It's very close! ğŸ˜€


I actually need the two rows you deleted because in my case, the same ticket can be closed and re-opened. If that happens, the count of days is reset and should start again at 1. Basically, one row = one ticket even if the TicketName is the same.

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

    Thanks, it worked for me! ğŸ˜€

  • Marfield's avatar
    Marfield
    Icon for Helper I rankHelper I
    1 year ago

    Is it possible by any chance to have the ticket status in the ticket table so that I don't multiply my rows in the dimension or so that I don't create a new dimension?