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.

 

Samarth_18
Super User
Super User

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


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


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!
!! Subscribe to my youtube Channel !!

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!