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
Community Champion

## Re: Non Working Days Calculation

Highlighted
Community Champion

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

6 REPLIES 6
Highlighted
Community Champion

## Re: Non Working Days Calculation

Highlighted
Community Champion

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

Highlighted
Super User IV

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

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
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 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors