The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
83 | |
72 | |
49 | |
41 |
User | Count |
---|---|
139 | |
113 | |
74 | |
64 | |
63 |