cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User V

Highlighted
Super User VI

## Re: Non Working Days Calculation

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

Proud to be a Super User!

6 REPLIES 6
Highlighted
Super User V

Highlighted
Super User VI

## Re: Non Working Days Calculation

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

Proud to be a Super User!

Highlighted
Super User IX

## Re: Non Working Days Calculation

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

Proud to be a Super User!

Regular Visitor

## Re: Non Working Days Calculation

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

Highlighted
Regular Visitor

Hi harshnathani

Highlighted
Regular Visitor

## Re: Non Working Days Calculation

Thanks for the help mohoneypat, appreciate it

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors