Non Working Days Calculation

[Ding_Dong43]

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!

[harshnathani · Accepted solution]

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

https://businessintelligist.com/2018/12/04/working-with-workdays-in-power-bi-translating-workdays-in...

Regards,

Harsh Nathani

[amitchandak]

@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]

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!!!

[mahoneypat · Accepted solution]

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]

Thanks for the help mohoneypat, appreciate it

[harshnathani · Accepted solution]

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

https://businessintelligist.com/2018/12/04/working-with-workdays-in-power-bi-translating-workdays-in...

Regards,

Harsh Nathani

[Ding_Dong43]

Hi harshnathani

Thanks for the advice