Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

Best Regards,
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)

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.