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

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
Icon for Helper I rankHelper 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
    Icon for Resolver I rankResolver 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.

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    1 year ago

    This is exatcly my result, what are you saying this is not what you are looking for?