How to calculate working days

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

[Greg_Deckler]

@venal You should be able to modify this approach: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

[venal]

Hi @Greg_Deckler ,

Thank you for the response.

In the above scenario, weekoffs are changing as per region.

Ex:- I have a data for Dubai, Please refer the above snippet and suggest.

Thank you in advance.

@venal 

[VahidDM]

Hi @venal 

Add a Week Day column to your calendar table by using this measure:

Week day = WEEKDAY('Calendar'[Date])

Then Try this code to add Working Days column into the main table:

Working Days = 
VAR _Order = 'Table'[OrderDate]
VAR _Delivery = 'Table'[DeliveryDate]
VAR _DateTable =
    FILTER (
        'Calendar',
        'Calendar'[Date] >= _Order
            && 'Calendar'[Date] <= _Delivery
    )
VAR _dubaiWD =
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        FILTER ( _DateTable, 'Calendar'[Week day] <> 6 && 'Calendar'[Week day] <> 7 )
    )
VAR _NotDubaiWD =
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        FILTER ( _DateTable, 'Calendar'[Week day] <> 1 && 'Calendar'[Week day] <> 7 )
    )
RETURN
    IF ( 'Table'[Region] = "Dubai", _dubaiWD, _NotDubaiWD )

Output:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[CNENFRNL]

PQ solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvY3NDAwVNJRcilNSswE0ob65vpGBkYgIWMDGDtWB6rSCCgc6g0kzBHKjIwxlBkDhZ3zc/JzkzITQSqQDDWygKuOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesID = _t, Region = _t, OrderDate = _t, DeliveryDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", type text}, {"Region", type text}, {"OrderDate", type date}, {"DeliveryDate", type date}}, "fr"),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "NetWorkday_PQ",
        each let l = List.Dates([OrderDate],Duration.Days([DeliveryDate]-[OrderDate])+1,#duration(1,0,0,0))
            in
                if [Region] = "Dubai" then
                    List.Accumulate(l,0,(s,c)=>s+Number.From(Date.DayOfWeek(c,Day.Sunday)<5))
                else List.Accumulate(l,0,(s,c)=>s+Number.From(Date.DayOfWeek(c,Day.Monday)<5))
    )
in
    #"Added Custom"

DAX solution

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

Regards,

@venal