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!
m4ni's avatar
m4ni
Icon for Resolver I rankResolver 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
Icon for Helper I rankHelper 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.