Forum Discussion
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!
Hi Ding_Dong43 ,
You can have a look at this blog to discount non working days.
https://www.sqlbi.com/articles/counting-working-days-in-dax/
https://www.youtube.com/watch?v=9M1V_m-oEzc&vl=en
Regards,
Harsh Nathani
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
6 Replies
Hi Ding_Dong43 ,
You can have a look at this blog to discount non working days.
https://www.sqlbi.com/articles/counting-working-days-in-dax/
https://www.youtube.com/watch?v=9M1V_m-oEzc&vl=en
Regards,
Harsh Nathani
Hi harshnathani
Thanks for the advice
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
Thanks for the help mohoneypat, appreciate it
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))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!!!
