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!

saud968's avatar
saud968
Icon for Memorable Member rankMemorable Member
2 years ago

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

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