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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.