Forum Discussion

alex320's avatar
alex320
New Member
3 years ago
Solved

Average per time intervall

Hi,

 

I want to calculate how the average ticket handling time changes over time. I have service tickets in Jira, which take a certain time to be solved. 

TicketClosed dateDuration (min)
115.1.20224
219.1.20227
33.2.20222
49.2.20229
527.2.20225
61.3.20227
79.3.20228

 

I want to document that by end of January the average handling time was (4+7)/2. In Febrary (incl. the history of January) average time was (4+7+2+9+5)/5. In March (incl. the history of January+February) average time was (4+7+2+9+5+7+8)/7.  

 

Thanks

Alex

Need Help
  • olgad's avatar
    olgad
    3 years ago

    Hi Alex, 
    calculate cumulative total duration, cumulative rows and dived duration by rows.

     

    Output =
    var CumulativeRows=CALCULATE(
       COUNTROWS('Table'),
        FILTER(
            ALL('Table'),
            'Table'[Closed Date]<=Max('Table'[Closed Date])
        )
    )

    var CumulativeDurationMin=CALCULATE(
       Sum('Table'[Duration (min)]),
        FILTER(
            ALL('Table'),
            'Table'[Closed Date]<=Max('Table'[Closed Date])
        )
    )

    RETURN
    Divide([Cumulative Total], [Cumulative Rows])

2 Replies

  • olgad's avatar
    olgad
    Resident Rockstar
    3 years ago

    Hi Alex, 
    calculate cumulative total duration, cumulative rows and dived duration by rows.

     

    Output =
    var CumulativeRows=CALCULATE(
       COUNTROWS('Table'),
        FILTER(
            ALL('Table'),
            'Table'[Closed Date]<=Max('Table'[Closed Date])
        )
    )

    var CumulativeDurationMin=CALCULATE(
       Sum('Table'[Duration (min)]),
        FILTER(
            ALL('Table'),
            'Table'[Closed Date]<=Max('Table'[Closed Date])
        )
    )

    RETURN
    Divide([Cumulative Total], [Cumulative Rows])