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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AdrianLock
Helper I
Helper I

Duration of a ticket in minutes excluding weekends(only) & taking away hold time

I'm after some help I've looked at countless post around time and perhaps I'm getting just a little confused.  I want a calculated column(not power Query).   Which gives me length of a ticket in minutes but excluding weekends (Not worrying about working time).  I then need to take away from that number of hours on hold.  This is already worked out in another field.   

I found the following code already however this include working hours.  Which I have removed.   Changed to anwser to minutes not hours.  As calls can be as short as minute or be months.  So lower denominator is better

 

 

 

 

TicketLength (Min) =
 VAR _WorkingMinutes=
 SUMX(
    CALCULATETABLE(
        IV_dim_CALENDAR,
        DATESBETWEEN(IV_dim_CALENDAR[Date],IV_fact_TicketsAll[CreatedDateTime],IV_fact_TicketsAll[ResolvedDateTime]),
        IV_dim_CALENDAR[IsWeekend] = FALSE()
    ),
    1440 * (IV_fact_TicketsAll[ResolvedDateTime] - IV_fact_TicketsAll[CreatedDateTime] - IV_fact_TicketsAll[TimeOnHold(Minutes)])
)
RETURN
IF(_WorkingMinutes,_WorkingMinutes,0)

 

 

 

 


Issue is I'm getting close to same anwser as datediff when just looking and very short ticket lengths but it seems to be rounding some up where datediff is not.  Is above the correct way to get this or am I missing something.  I tried using Int function etc but these did not seem to solve my problem.   

 

Test column is datediff and highlighted is the code above.   See picture below: -

 

dates.JPG

 

1 REPLY 1
v-yanjiang-msft
Community Support
Community Support

Hi @AdrianLock ,

I create a sample to reproduce your problem.

vyanjiangmsft_0-1686821412690.png

I used the formula you provided, then get the result 1440. In my sample, ResolvedDateTime 6/10/2023 is saturday, do you mean the result in my sample should be 240(4hour*60)?

 

Best regards,

Community Support Team_yanjiang

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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