cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alex320
New Member

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

1 ACCEPTED SOLUTION
olgad
Solution Sage
Solution Sage

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

 

olgad_0-1673543636984.png

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])

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

2 REPLIES 2
alex320
New Member

Thanks a lot, exactly what I needed 👍

olgad
Solution Sage
Solution Sage

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

 

olgad_0-1673543636984.png

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])

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

Top Kudoed Authors