cancel
Showing results for
Did you mean:
Memorable Member

## how to calculate working days with different regions

Hi,

 SalesID Region OrderDate DeliveryDate WorkingDays SO1001 Dubai 1/7/2021 30/7/2021 22 SO1002 UK 7/7/2021 23/7/2021 13 SO1003 Colombia 21/7/2021 28/7/2021 6

How to calculate Working Days.

1. Dubai - Exclude "Friday & Saturday"

2. UK & Colombia - Exclude "Saturday & Sunday".

I have created a "Calendar" table with the below query.

calendar dim = CALENDAR(IF(MONTH(TODAY()) > 3, CONCATENATE(YEAR(TODAY())-1, "-04-01"), CONCATENATE(YEAR(TODAY())-2, "-04-01")), TODAY())

1 ACCEPTED SOLUTION
Community Support

Hi @venal ,

In the calendar table, create the following two columns

``````Dubai - Exclude "Friday & Saturday" =
var _week=WEEKDAY([Date],2)
return IF(_week=5||_week=6,0,1)``````
``````UK & Colombia - Exclude "Saturday & Sunday" =
var _week=WEEKDAY([Date],2)
return IF(_week=6||_week=7,0,1)``````

Then create the calculated column in the main table

``````WorkingDays =
SWITCH (
[Region],
"Dubai",
CALCULATE (
COUNTROWS ( 'Table 2' ),
FILTER (
'Table 2',
[Date] <= [DeliveryDate]
&& [Date] >= [OrderDate]
&& [Dubai - Exclude "Friday & Saturday"] = 1
)
),
"UK",
CALCULATE (
COUNTROWS ( 'Table 2' ),
FILTER (
'Table 2',
[Date] <= [DeliveryDate]
&& [Date] >= [OrderDate]
&& [UK & Colombia - Exclude "Saturday & Sunday"] = 1
)
),
"Colombia",
CALCULATE (
COUNTROWS ( 'Table 2' ),
FILTER (
'Table 2',
[Date] <= [DeliveryDate]
&& [Date] >= [OrderDate]
&& [UK & Colombia - Exclude "Saturday & Sunday"] = 1
)
)
)
``````

The working days of SO1001 should be 21. Please check again.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Memorable Member

Hi Team,

Thank you everyone for your suggestions.

One more validation need to add, For example Dubai national holidays need to exclude and in the same way other region national holidays also exclude.

Please find the below sample data.

 Region Holiday Dubai 1/1/2021 Dubai 11/5/2021 Dubai 12/5/2021 Dubai 13/5/2021 Dubai 14/5/2021 Dubai 15/5/2021 Dubai 19/7/2021 Dubai 20/7/2021 Dubai 21/7/2021 Dubai 22/7/2021 Dubai 12/8/2021 Dubai 18/10/2021 Dubai 19/10/2021 Dubai 30/11/2021 Dubai 2/12/2021 UK 1/1/2021 UK 2/4/2021 UK 5/4/2021 UK 3/5/2021 UK 31/5/2021 UK 30/8/2021 UK 27/12/2021 UK 28/12/2021

Thank you in advance.

Community Support

Hi @venal ,

In the calendar table, create the following two columns

``````Dubai - Exclude "Friday & Saturday" =
var _week=WEEKDAY([Date],2)
return IF(_week=5||_week=6,0,1)``````
``````UK & Colombia - Exclude "Saturday & Sunday" =
var _week=WEEKDAY([Date],2)
return IF(_week=6||_week=7,0,1)``````

Then create the calculated column in the main table

``````WorkingDays =
SWITCH (
[Region],
"Dubai",
CALCULATE (
COUNTROWS ( 'Table 2' ),
FILTER (
'Table 2',
[Date] <= [DeliveryDate]
&& [Date] >= [OrderDate]
&& [Dubai - Exclude "Friday & Saturday"] = 1
)
),
"UK",
CALCULATE (
COUNTROWS ( 'Table 2' ),
FILTER (
'Table 2',
[Date] <= [DeliveryDate]
&& [Date] >= [OrderDate]
&& [UK & Colombia - Exclude "Saturday & Sunday"] = 1
)
),
"Colombia",
CALCULATE (
COUNTROWS ( 'Table 2' ),
FILTER (
'Table 2',
[Date] <= [DeliveryDate]
&& [Date] >= [OrderDate]
&& [UK & Colombia - Exclude "Saturday & Sunday"] = 1
)
)
)
``````

The working days of SO1001 should be 21. Please check again.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Memorable Member

Hi @venal ,

You can create a column with below code:-

``````Workdays =
IF (
working_days[Region] IN { "UK", "Colombia" },
COUNTROWS (
FILTER (
CALENDAR ( working_days[OrderDate], working_days[DeliveryDate] ),
"Day of Week", WEEKDAY ( [Date], 2 )
),
[Day of Week] <> 6
&& [Day of Week] <> 7
)
),
IF (
working_days[Region] IN { "Dubai" },
COUNTROWS (
FILTER (
CALENDAR ( working_days[OrderDate], working_days[DeliveryDate] ),
"Day of Week", WEEKDAY ( [Date], 2 )
),
[Day of Week] <> 5
&& [Day of Week] <> 6
)
)
)
)``````

Thanks,

Samarth

Super User

@venal , a new column like

var _1 = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
var _2 = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if( not WEEKDAY([Date],2) in {5,6} ,1,0)),[WorkDay] =1))
return
if([Region] in {"Dubai"} , _2, _1)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Memorable Member

Hi Team,

Thank you everyone for your suggestions.

One more validation need to add, For example Dubai national holidays need to exclude and in the same way other region national holidays also exclude.

Please find the below sample data.

 Region Holiday Dubai 1/1/2021 Dubai 11/5/2021 Dubai 12/5/2021 Dubai 13/5/2021 Dubai 14/5/2021 Dubai 15/5/2021 Dubai 19/7/2021 Dubai 20/7/2021 Dubai 21/7/2021 Dubai 22/7/2021 Dubai 12/8/2021 Dubai 18/10/2021 Dubai 19/10/2021 Dubai 30/11/2021 Dubai 2/12/2021 UK 1/1/2021 UK 2/4/2021 UK 5/4/2021 UK 3/5/2021 UK 31/5/2021 UK 30/8/2021 UK 27/12/2021 UK 28/12/2021

Thank you in advance.

Memorable Member

Thank you for the response.

If i use the above measure in "Calendar Dim" table, I can't use the table for other regions.

Because exclude Dubai, rest of all regions are "Saturday and Sunday" weekoff.

So need to exclude "Saturday and Sunday" - UK, Colombia (as per above data)

"Friday and Saturday" - Dubai

Ex:- The below sales order, Ordered Date - 1/Jul/21 & Delivered Date - 30/Jul/21 -> Total working days to deliver is 22.

 SO1001 Dubai 1/7/2021 30/7/2021 22

Please check the above info and suggest.

Announcements