Forum Discussion

JawaharPrem6's avatar
JawaharPrem6
Icon for Helper I rankHelper I
1 year 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
    1 year 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
    1 year 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!

saud968's avatar
saud968
Icon for Memorable Member rankMemorable Member
1 year ago

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's avatar
JawaharPrem6
Icon for Helper I rankHelper I
1 year 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
    1 year 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!