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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.