Forum Discussion

Craig_01's avatar
Craig_01
Helper I
2 years ago
Solved

Network Days DAX Issue calculation is adding an extra day, same day should be 0

Hi All,

 

Any suggestions, i am using the Networkday DAX function and have a holiday table etc.. It seems to be calculating correctly by excluding weekends.. However if my promised despatch date is 08/07/2024, and the actual despatch date is 08/07/2024 i am getting a value of 1... Where in my eyes this should be 0... As its not late or early from a delivery performance point of view.

 

Regards

Craig

 

Need Help
Show and Tell
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Craig_01 -you can modify the dax logic as below,hpe you have a holiday table in your datamodel, it correctly handling cases

     

    NetworkDaysAdjusted =
    VAR PromisedDate = [Promised Despatch Date]
    VAR ActualDate = [Actual Despatch Date]
    VAR HolidaysTable = 'Holidays'[Date]
    VAR NetworkDaysCalculation =
    COUNTROWS(
    FILTER(
    CALENDAR(PromisedDate, ActualDate),
    NOT(ISBLANK([Date])) &&
    WEEKDAY([Date],2) <= 5 && // Excludes weekends
    NOT([Date] IN HolidaysTable) // Excludes holidays
    )
    )
    RETURN
    IF(PromisedDate = ActualDate, 0, NetworkDaysCalculation - 1)

     

    hope it works. 

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Craig_01 -you can modify the dax logic as below,hpe you have a holiday table in your datamodel, it correctly handling cases

     

    NetworkDaysAdjusted =
    VAR PromisedDate = [Promised Despatch Date]
    VAR ActualDate = [Actual Despatch Date]
    VAR HolidaysTable = 'Holidays'[Date]
    VAR NetworkDaysCalculation =
    COUNTROWS(
    FILTER(
    CALENDAR(PromisedDate, ActualDate),
    NOT(ISBLANK([Date])) &&
    WEEKDAY([Date],2) <= 5 && // Excludes weekends
    NOT([Date] IN HolidaysTable) // Excludes holidays
    )
    )
    RETURN
    IF(PromisedDate = ActualDate, 0, NetworkDaysCalculation - 1)

     

    hope it works. 

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Craig_01's avatar
      Craig_01
      Helper I
      2 years ago

      rajendraongole1 Thanks for reply.. Confused however how to impliment this, see below my current formula.

       

      NetworkDaysRequested =
          NETWORKDAYS(
          SELECTEDVALUE(TLiveDespatchValue[RequestedDeliveryDate]),
          SELECTEDVALUE(LiveDespatchValue[DespatchDate]),
          1,
          HolidaysTable
          )
       
      Regards