cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
venal
Memorable Member
Memorable Member

how to calculate working days with different regions

Hi,

 

SalesIDRegionOrderDateDeliveryDateWorkingDays
SO1001Dubai1/7/202130/7/202122
SO1002UK7/7/202123/7/202113
SO1003Colombia21/7/202128/7/20216

 

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.

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
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)

4.png

 

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

5.png

 

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.

 

View solution in original post

6 REPLIES 6
venal
Memorable Member
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.

 

RegionHoliday
Dubai1/1/2021
Dubai11/5/2021
Dubai12/5/2021
Dubai13/5/2021
Dubai14/5/2021
Dubai15/5/2021
Dubai19/7/2021
Dubai20/7/2021
Dubai21/7/2021
Dubai22/7/2021
Dubai12/8/2021
Dubai18/10/2021
Dubai19/10/2021
Dubai30/11/2021
Dubai2/12/2021
UK1/1/2021
UK2/4/2021
UK5/4/2021
UK3/5/2021
UK31/5/2021
UK30/8/2021
UK27/12/2021
UK28/12/2021

 

Thank you in advance.

 

@venal 

v-stephen-msft
Community Support
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)

4.png

 

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

5.png

 

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.

 

View solution in original post

Samarth_18
Memorable Member
Memorable Member

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
Super User
Super User

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

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!

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.

 

RegionHoliday
Dubai1/1/2021
Dubai11/5/2021
Dubai12/5/2021
Dubai13/5/2021
Dubai14/5/2021
Dubai15/5/2021
Dubai19/7/2021
Dubai20/7/2021
Dubai21/7/2021
Dubai22/7/2021
Dubai12/8/2021
Dubai18/10/2021
Dubai19/10/2021
Dubai30/11/2021
Dubai2/12/2021
UK1/1/2021
UK2/4/2021
UK5/4/2021
UK3/5/2021
UK31/5/2021
UK30/8/2021
UK27/12/2021
UK28/12/2021

 

Thank you in advance.

 

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

SO1001Dubai1/7/202130/7/202122

Please check the above info and suggest.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors