Forum Discussion

JawaharPrem6's avatar
JawaharPrem6
Icon for Helper I rankHelper I
2 years ago
Solved

Network Days calculation with Calander and holidays

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   out...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    saud968
    2 years ago

    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's avatar
JawaharPrem6
Icon for Helper I rankHelper I
2 years ago

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's avatar
saud968
Icon for Memorable Member rankMemorable Member
2 years ago

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!