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 aft...
Need Help
Show and Tell
amitchandak's avatar
amitchandak
Super 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!!!