Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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/LIKE!
PROUD TO BE A SUPER USER!
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
Super User
Super User

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/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors