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
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: -
Hi @AdrianLock ,
I create a sample to reproduce your problem.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
94 | |
86 | |
76 | |
64 |
User | Count |
---|---|
138 | |
113 | |
107 | |
98 | |
92 |