how to calculate working days with different regions

[venal]

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

Can you please help us.

[venal]

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.

@venal 

[v-stephen-msft · Accepted solution]

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.

[Samarth_18]

Hi @venal ,

You can create a column with below code:-

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

Thanks,

Samarth

[amitchandak]

@venal , a new column like

BusinessDay =
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)

[venal]

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.

@venal 

[venal]

Hi @amitchandak 

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.