Help with calculating business days and decimal places

[lewisgrantevans]

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 -

Turnaround Business Days =

CALCULATE(

COUNTROWS('Calendar'),

DATESBETWEEN(

'Calendar'[Date],

'Case Assignment History'[Entry_Time__c],

'Case Assignment History'[Exit_Time__c] ),

'Calendar'[IsWorkingDay] = TRUE,

ALLSELECTED( 'Case Assignment History' ))

This is working and creating a 'Turnaround Business Days' column and removing weekends, however, I need to incorporate 2 decimal points into the calculation, because everything is being rounded up or down. For the following example, the desired result is '0.24', rather than '1' -

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

[Fowmy]

@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

[lewisgrantevans]

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!

[Fowmy]

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

[lewisgrantevans]

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

[Fowmy]

@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 

[lewisgrantevans]

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!

[Fowmy · Accepted 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

[lewisgrantevans]

Amazing, thank you very much Sir! Have a great weekend

[Fowmy]

@lewisgrantevans 

What is your working day definition? Saturday and Sunday ? Do you have any other days not marked as non working days like holidays?