Forum Discussion
Need Difference between dates minus weekends and holidays
I am creating a report that needs to display the delta between 2 dates, minus weekends and holidays. I tried network days, but for some reason BI would not recognize NETWORKDAYS.
We used to accomplish this task in Excel by using =networkdays(f2,g2)-1 and then changing any 3s to a 2 for a month that has a holiday.
I have a date table and a holiday table. The date table is linked to the main table by the date (G). I have created a column in the holiday table with just the month number and linked that with a month number in the main table.
Is there a way to use this to get to the final outcome needed below?
| F | G | Delta F & G | After Formula | Final Outcome |
| 12/23/2022 19:26 | 12/28/2022 | 5 | 3 | 2 |
| 12/16/2022 19:37 | 12/17/2022 | 1 | 0 | 0 |
| 12/21/2022 17:16 | 12/23/2022 | 2 | 2 | 2 |
| 12/22/2022 21:06 | 12/23/2022 | 1 | 1 | 1 |
| 12/23/2022 12:17 | 12/27/2022 | 4 | 2 | 2 |
| 12/2/2022 20:48 | 12/5/2022 | 3 | 1 | 1 |
| 12/7/2022 19:26 | 12/9/2022 | 2 | 2 | 2 |
| 12/8/2022 19:22 | 12/12/2022 | 4 | 2 | 2 |
| 12/27/2022 18:45 | 12/29/2022 | 2 | 2 | 2 |
| 12/19/2022 21:12 | 12/20/2022 | 1 | 1 | 1 |
| 12/20/2022 11:47 | 12/22/2022 | 2 | 2 | 2 |
| 12/22/2022 19:20 | 12/27/2022 | 5 | 3 | 2 |
| 12/21/2022 21:28 | 12/22/2022 | 1 | 1 | 1 |
| 12/23/2022 12:28 | 12/28/2022 | 5 | 3 | 2 |
7 Replies
SivaManiResident Rockstar
RobertReeves , You may need to maintain the holidays. I found the below post with steps to create a calendar table,
Once you have the above table, you can take the count of working days by filtering the dates.
- Sahir_Maharaj
Super User
Yes, you can use DAX to calculate the final outcome needed. You can create a calculated column using DAX to determine the number of workdays between two dates, excluding weekends and holidays.
- Sahir_Maharaj
Here is an example of a DAX formula that could be used for this calculation:
=IF(ISBLANK([Delta F & G]),BLANK(),IF(MONTH(G) = RELATED(Holiday[Month Number]), [Delta F & G] - 2, [Delta F & G] -1))
Assuming that the date table is linked to the main table by the date column (G), the holiday table is linked to the main table by the month number column, and the "Delta F & G" column is the number of days between the two dates.
- Sahir_Maharaj
The above formula first checks if the "Delta F & G" column is blank. If it is, it returns a blank. If not, it checks if the month of the date in column G is the same as the related month number in the holiday table. If it is, it subtracts 2 from the "Delta F & G" value. If not, it subtracts 1.
This will give you the "Final Outcome" column as in the example you provided.
PadycosmosSolution Sage
Could you just use index columns in your calendar?
Add a weekday name column and filter out Sat and Sunday.
Import a holiday table and merge it to your calendar. (you can link to an online or a company one) Then just filter by entry = "null"
Add 2 index columns one starting at 0 the next starting at 1.
Merge the query to itself with the first column being index starting at 0 then starting at 1 and add the date column and label it previous date.
Then you will end up with both dates in the same row and can add whatever calculated column you would like to use on the two dates.
