How to calculate difference between two date/time in seconds excluding weekends and holidays .

[Anonymous]

Hi All,

I need to calculate difference between this Created_date and Credit_hold_date columns in seconds by excluding weekends and holidays. 

Any help would be appreciated . 

sample data : 

table 1 

Created_date	Credit_hold_date
5/07/19 09:52:27 AM	5/07/19 12:09:07 PM
5/21/19 08:24:52 AM	5/21/19 02:14:12 PM
6/11/19 02:03:33 PM	6/11/19 04:36:44 PM
5/23/19 04:39:14 PM	5/29/19 01:58:15 PM
5/29/19 11:43:57 AM	5/30/19 02:19:15 PM
5/30/19 03:15:07 PM	6/03/19 05:05:27 PM
7/04/19 03:10:49 PM	7/05/19 09:44:30 AM

6/28/19 04:01:49 PM

7/02/19 09:04:33 AM

Table 2

Date	Holidays
01/01/2019	New Year's Day
02/18/2019	Family Day
04/19/2019	Good Friday
05/20/2019	Victoria Day
07/01/2019	Canada Day(observed)
08/05/2019	Civic Holiday
09/02/2019	Labour Day
10/14/2019	Thanksgiving Day
12/25/2019	Christmas Day
12/26/2019	Boxing Day

Regards,

Khushboo

[Anonymous]

No .

While calculating difference between created/hold date it should exclude weekends and holidays.

[tex628]

That makes things alot more simple. 

You should start by creating a column counting the number of red days / weekends between the dates on each row. 

HolidayCount = 
VAR holdDate = 'Table1'[Credit hold date]
VAR createDate = 'Table1'[Created_date]
Return
Calculate(
Countrows('Table2'),
all('Table2'),
'Table2'[Date]<= holdDate ,
'Table2'[Date]>= createDate
)

[Anonymous]

Thanks , But My scope is to get the difference between those two columns in seconds by excluding holiday and weekend,

[tex628]

It was only the first step. We need that number to be able to know how many seconds to subtract in the next step. 
Are you getting the correct number of red days for each row?

[Anonymous]

Yes , How can i exclude weekends?

[tex628]

Difference in seconds;

Difference = (Table[Credit_Hold_Date] - Table[Created_Date]) * 24 * 60

Then subtract the seconds from the red days

Difference = ((Table[Credit_Hold_Date] - Table[Created_Date]) * 24 * 60) - (86400 * Table[HolidayCount])

Should give you the correct amount of seconds between the 2 dates, red days excluded.

[Anonymous]

I am not getting holiday count even if difference of dates has holidays i.e. July 1 2019Holiday Table

Dax functionValues highlighted

[Anonymous · Accepted solution]

It worked by changing some code.

App_hold_holidays =

COUNTROWS (

FILTER (

'Federal Holiday',

AND (

'Federal Holiday'[Date] >= MApproved_hold_ReleasedManually[Approved_date_time],

'Federal Holiday'[Date] <= MApproved_hold_ReleasedManually[Credit_hold_date_time]

)

)

)

Thanks.

[tex628]

Can the created/hold date occur on a holiday/weekend?