Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
It worked by changing some code.
No .
While calculating difference between created/hold date it should exclude weekends and holidays.
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
)
Thanks , But My scope is to get the difference between those two columns in seconds by excluding holiday and weekend,
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?
Yes , How can i exclude weekends?
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.
I am not getting holiday count even if difference of dates has holidays i.e. July 1 2019
It worked by changing some code.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |