Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello,
I have created a Calendar table in Power Bi so I can track working days and this is working as intended. I am then using the following DAX to create a 'turnaround business days' column -
Entry_Time__c | Exit_Time__c | Elapsed_Time_Days__c | Turnaround Business Days | Expected Result |
12/08/2019 07:48 | 12/08/2019 13:27 | 0.24 | 1 | 0.24 |
Any help is much appreciated!
Thanks
Solved! Go to Solution.
@lewisgrantevans
I foud the issue, I set it as TRUE for working day, it should be false:
Turnaround Business Days =
var __workdays =
CALCULATE(
COUNTROWS('Calendar'),
DATESBETWEEN(
'Calendar'[Date],
'Case Assignment History'[Entry_Time__c],
'Case Assignment History'[Exit_Time__c] ),
'Calendar'[IsWorkingDay] = FALSE
)
var __days = CONVERT('Case Assignment History'[Exit_Time__c]-'Case Assignment History'[Entry_Time__c],DOUBLE)
return
__days - __workdays
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@lewisgrantevans
Can you check if this calculated column works for you?
Turnaround Business Days =
var __workdays =
CALCULATE(
COUNTROWS('Calendar'),
DATESBETWEEN(
'Calendar'[Date],
'Case Assignment History'[Entry_Time__c],
'Case Assignment History'[Exit_Time__c] ),
'Calendar'[IsWorkingDay] = TRUE
)
var __days = CONVERT('Case Assignment History'[Exit_Time__c]-'Case Assignment History'[Entry_Time__c],DOUBLE)
var __result = IF( __days >= __workdays , __days - __workdays )
return
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I'm getting a syntax error unfortunately -
The syntax for ')' is incorrect. (DAX(var __workdays = CALCULATE( COUNTROWS('Calendar'), DATESBETWEEN( 'Calendar'[Date], 'Case Assignment History'[Entry_Time__c], 'Case Assignment History'[Exit_Time__c] ), 'Calendar'[IsWorkingDay] = TRUE )var __days = CONVERT('Case Assignment History'[Exit_Time__c]-'Case Assignment History'[Entry_Time__c],DOUBLE)var __result = IF( __days >= __workdays , __days - __workdays ) return)).
Thanks!
Turnaround Business Days =
var __workdays =
CALCULATE(
COUNTROWS('Calendar'),
DATESBETWEEN(
'Calendar'[Date],
'Case Assignment History'[Entry_Time__c],
'Case Assignment History'[Exit_Time__c] ),
'Calendar'[IsWorkingDay] = TRUE
)
var __days = CONVERT('Case Assignment History'[Exit_Time__c]-'Case Assignment History'[Entry_Time__c],DOUBLE)
var __result = IF( __days >= __workdays , __days - __workdays )
return
__result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I think we're almost there!
It appears to be calculating the wrong way around, so anything with a 'negative' value is returning a blank. Your column is 'Turnaround Business Days 2' -
Entry_Time__c | Exit_Time__c | Elapsed_Time_Days__c | Turnaround Business Days | Turnaround Business Days 2 |
28/06/2019 09:10 | 04/07/2019 09:33 | 6.02 | 5 | 1.02 |
05/06/2019 10:03 | 05/06/2019 15:18 | 0.22 | 1 | |
19/06/2019 08:35 | 19/06/2019 11:11 | 0.11 | 1 | |
20/06/2019 14:10 | 20/06/2019 15:23 | 0.05 | 1 | |
05/07/2019 13:23 | 05/07/2019 15:15 | 0.08 | 1 | |
08/07/2019 15:40 | 15/07/2019 10:18 | 6.78 | 6 | 0.78 |
09/07/2019 13:44 | 09/07/2019 14:54 | 0.05 | 1 | |
11/07/2019 08:05 | 22/07/2019 14:15 | 11.26 | 8 | 3.26 |
12/07/2019 11:19 | 15/07/2019 09:36 | 2.93 | 2 | 0.93 |
22/07/2019 13:25 | 22/07/2019 15:42 | 0.1 | 1 | |
15/07/2019 08:21 | 15/07/2019 11:05 | 0.11 | 1 |
@lewisgrantevans
If you want the negative value, it will be shown as follows
Turnaround Business Days =
var __workdays =
CALCULATE(
COUNTROWS('Calendar'),
DATESBETWEEN(
'Calendar'[Date],
'Case Assignment History'[Entry_Time__c],
'Case Assignment History'[Exit_Time__c] ),
'Calendar'[IsWorkingDay] = TRUE
)
var __days = CONVERT('Case Assignment History'[Exit_Time__c]-'Case Assignment History'[Entry_Time__c],DOUBLE)
return
__days - __workdays
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I really appreciate the time you've taken to help and this is way closer than i could have gotten by myself! However, it's still not giving me the expected value. I'm trying to achieve Elapsed_Time_Days_c minus the weekends -
Strangely enough the line highlighted in green is calculating correctly!
Here's the same but in text form -
Entry_Time__c | Exit_Time__c | Elapsed_Time_Days__c | Turnaround Business Days | Turnaround Business Days 2 | Expected Values |
28/06/2019 09:10 | 04/07/2019 09:33 | 6.02 | 5 | 1.02 | 4.02 |
05/06/2019 10:03 | 05/06/2019 15:18 | 0.22 | 1 | -0.78 | 0.22 |
19/06/2019 08:35 | 19/06/2019 11:11 | 0.11 | 1 | -0.89 | 0.11 |
20/06/2019 14:10 | 20/06/2019 15:23 | 0.05 | 1 | -0.95 | 0.05 |
05/07/2019 13:23 | 05/07/2019 15:15 | 0.08 | 1 | -0.92 | 0.08 |
08/07/2019 15:40 | 15/07/2019 10:18 | 6.78 | 6 | 0.78 | 4.78 |
09/07/2019 13:44 | 09/07/2019 14:54 | 0.05 | 1 | -0.95 | 0.05 |
11/07/2019 08:05 | 22/07/2019 14:15 | 11.26 | 8 | 3.26 | 7.26 |
12/07/2019 11:19 | 15/07/2019 09:36 | 2.93 | 2 | 0.93 | 0.93 |
22/07/2019 13:25 | 22/07/2019 15:42 | 0.1 | 1 | -0.9 | 0.1 |
15/07/2019 08:21 | 15/07/2019 11:05 | 0.11 | 1 | -0.89 | 0.11 |
Thank you!
@lewisgrantevans
I foud the issue, I set it as TRUE for working day, it should be false:
Turnaround Business Days =
var __workdays =
CALCULATE(
COUNTROWS('Calendar'),
DATESBETWEEN(
'Calendar'[Date],
'Case Assignment History'[Entry_Time__c],
'Case Assignment History'[Exit_Time__c] ),
'Calendar'[IsWorkingDay] = FALSE
)
var __days = CONVERT('Case Assignment History'[Exit_Time__c]-'Case Assignment History'[Entry_Time__c],DOUBLE)
return
__days - __workdays
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Amazing, thank you very much Sir! Have a great weekend
@lewisgrantevans
What is your working day definition? Saturday and Sunday ? Do you have any other days not marked as non working days like holidays?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
124 | |
79 | |
49 | |
38 | |
37 |
User | Count |
---|---|
196 | |
80 | |
70 | |
51 | |
42 |