Backlog average age by day

[Marfield]

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.

[FBergamaschi]

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]

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

[FBergamaschi]

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

[m4ni]

@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.

[m4ni]

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]

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]

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]

You may find the file on dropbox here

Thanks for your time

[FBergamaschi]

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

[FBergamaschi]

you can find my pbix based on your data here with my code

https://drive.google.com/drive/folders/1ZtXvIpBJffCmR-aEbcean9iO-hGJBUwV?usp=sharing

I had to create a tcikets dimension and remove the last two rows of your table as the same ticket was repeated

It works to me, please let me know!

Best

[Marfield]

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 · Accepted solution]

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]

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?

[Marfield]

Thanks, it worked for me! 😀