Network Days calculation with Calander and holidays

[JawaharPrem6]

Hi all,

i have two days need to calculate network days with calander and holidays ouput required not days need in hours..

dates in BI Table

Calender table

holidays

output required

Thanks

Jawahar

[Anonymous · Accepted solution]

Hi @JawaharPrem6 ,

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[saud968 · Accepted solution]

Please accept the answer which resolved the issue for you. 

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[JawaharPrem6]

Hi Saud,

Previously i will accpect but i didnt get the accpect button hence delay...

[saud968]

No problem, I am happy that I was able to help you out. 

[Anonymous · Accepted solution]

Hi @JawaharPrem6 ,

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[saud968]

Ensure your date tables (Calendar and Holidays) are properly set up and related to your main table.
Create a measure to calculate the working hours:

daxCopyWorking Hours =
VAR StartDate = [Received_Date]
VAR EndDate = [Completed_Date]
VAR TotalHours =
DATEDIFF(StartDate, EndDate, HOUR)
VAR WorkingDays =
COUNTROWS(
FILTER(
CALENDAR(StartDate, EndDate),
AND(
NOT(WEEKDAY(Calendar[Date], 2) IN {6,7}),
NOT(Calendar[Date] IN VALUES(Holidays[Date]))
)
)
)
VAR WorkingHours = WorkingDays * 8
RETURN
IF(TotalHours <= 24,
TotalHours -
IF(WEEKDAY(StartDate, 2) IN {6,7} OR StartDate IN VALUES(Holidays[Date]),
HOUR(StartDate),
IF(HOUR(StartDate) < 9, 0, IF(HOUR(StartDate) >= 17, 8, HOUR(StartDate) - 9))
) -
IF(WEEKDAY(EndDate, 2) IN {6,7} OR EndDate IN VALUES(Holidays[Date]),
24 - HOUR(EndDate),
IF(HOUR(EndDate) <= 9, 0, IF(HOUR(EndDate) > 17, 8, HOUR(EndDate) - 9))
),
WorkingHours
)
This measure does the following:

Calculates the total hours between start and end dates
Counts working days, excluding weekends and holidays
For periods less than 24 hours, it adjusts for non-working hours
For longer periods, it uses a standard 8-hour workday

Add this measure to your table visualization.
Format the measure to display with 2 decimal places.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[JawaharPrem6]

Hi Saud Ansari, thanks for you time to sending this,  but iam getting error's, and at the time i searched and tried this dax is working fine and also check with networkday formula in excel the both getting same value same.  

WorkingHours =

VAR StartDate = [Received_Date]

VAR EndDate = [Completed_Date]

RETURN

IF(

    ISBLANK(StartDate) || ISBLANK(EndDate),

    BLANK(),

    (NETWORKDAYS(StartDate, EndDate) - 1 - MOD(StartDate, 1) + MOD(EndDate, 1)) * 24

)

Thanks.

[saud968]

I am sorry i am confused, is the below measure working for you

Working Hours = VAR StartDate = [Received_Date] VAR EndDate = [Completed_Date] VAR TotalHours = DATEDIFF(StartDate, EndDate, HOUR) VAR WorkingDays = COUNTROWS( FILTER( CALENDAR(StartDate, EndDate), AND( NOT(WEEKDAY(Calendar[Date], 2) IN {6,7}), NOT(Calendar[Date] IN VALUES(Holidays[Date])) ) ) ) VAR WorkingHours = WorkingDays * 8 RETURN IF(TotalHours <= 24, TotalHours - IF(WEEKDAY(StartDate, 2) IN {6,7} OR StartDate IN VALUES(Holidays[Date]), HOUR(StartDate), IF(HOUR(StartDate) < 9, 0, IF(HOUR(StartDate) >= 17, 8, HOUR(StartDate) - 9)) ) - IF(WEEKDAY(EndDate, 2) IN {6,7} OR EndDate IN VALUES(Holidays[Date]), 24 - HOUR(EndDate), IF(HOUR(EndDate) <= 9, 0, IF(HOUR(EndDate) > 17, 8, HOUR(EndDate) - 9)) ), WorkingHours )

[JawaharPrem6]

Hi, Yes below mentioned code is working for fine, and i share u a scree shot also..

WorkingHours =

VAR StartDate = [Received_Date]

VAR EndDate = [Completed_Date]

RETURN

IF(

    ISBLANK(StartDate) || ISBLANK(EndDate),

    BLANK(),

    (NETWORKDAYS(StartDate, EndDate) - 1 - MOD(StartDate, 1) + MOD(EndDate, 1)) * 24

)

iam getting error in your code "{6,7} OR EndDate IN VALUES(Holiday[Date]) is not a valid table." in 

[saud968]

VAR StartDate = [Received_Date] VAR EndDate = [Completed_Date] VAR TotalHours = DATEDIFF(StartDate, EndDate, HOUR) VAR WorkingDays = COUNTROWS( FILTER( CALENDAR(StartDate, EndDate), NOT(WEEKDAY(Calendar[Date], 2) IN {6,7}) && NOT(Calendar[Date] IN VALUES(Holiday[Date])) ) ) VAR WorkingHours = WorkingDays * 8 RETURN IF( TotalHours <= 24, TotalHours - IF( OR( WEEKDAY(StartDate, 2) IN {6,7}, StartDate IN VALUES(Holiday[Date]) ), HOUR(StartDate), IF( HOUR(StartDate) < 9, 0, IF(HOUR(StartDate) >= 17, 8, HOUR(StartDate) - 9) ) ) - IF( OR( WEEKDAY(EndDate, 2) IN {6,7}, EndDate IN VALUES(Holiday[Date]) ), 24 - HOUR(EndDate), IF( HOUR(EndDate) < 9, 0, IF(HOUR(EndDate) > 17, 8, HOUR(EndDate) - 9) ) ), WorkingHours )

Replaced the inline || operators with proper OR() function calls
Kept the && operator in the FILTER function where it's appropriate
Wrapped the logical conditions in OR() function for proper table operation handling

This should now work correctly. The measure will still:

Calculate total hours between dates
Account for weekends and holidays
Consider 9 AM to 5 PM working hours
Handle both short and long duration calculations

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!