Forum Discussion

Ding_Dong43's avatar
Ding_Dong43
Frequent Visitor
6 years ago
Solved

Non Working Days Calculation

Hi All My first post so be gentle!!! I have to determine is a transaction date is either "on time" or "late". The criteria to determine this is: On time = 5 days early of the request date, 0 days after the request date. Late = any date after the request date. What I need help with is how to discount non working days within the below formula: Del Status = if([Day Variation]>=0&&[Day Variation]<=5,"ON TIME", IF([Day Variation]<0,"LATE", "EARLY") Any help is greatly appreciated!

Need Help
Show and Tell

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    A good approach for this is to make a Date table with an IsWorkingDay column based on your work week and then you can write a simple expression to count how many working days occur between two dates.  This video/article gives the details.

     

    https://www.sqlbi.com/articles/counting-working-days-in-dax/

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Ding_Dong43's avatar
      Ding_Dong43
      Frequent Visitor
      6 years ago

      Thanks for the help mohoneypat, appreciate it

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Ding_Dong43 , create a date calendar with the following column

    Date = CALENDAR(date(2018,01,01), date(2021,12,31)) 
Work Day = if(WEEKDAY([Date],2)>=6,0,1) 
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

     

    You can travel across Working days like

    Plus 10 Days = var _max =maxx(ALLSELECTED('Date'),'Date'[Work Date cont Rank])
    return CALCULATE(Min('Date'[Date]),filter(ALL('Date'),'Date'[Work Date Rank] =_max+10))

    • Ding_Dong43's avatar
      Ding_Dong43
      Frequent Visitor
      6 years ago

      Hi Amit

       

      Thanks so much for taking the time to reply.

       

      Tried several ways with this, with varying results.

      Ended up doing the following:

      Del Status =
      if([DeliveryWorkingDays]>0&&[DeliveryWorkingDays]<=1,"ON TIME",
      IF([DeliveryWorkingDays]>1,"LATE", "EARLY"))

       

      Seems to work... but not as elegant perhaps, it's all a learning curve!!!