topic Re: How to calculate working days in DAX Commands and Tips

How to calculate working days

venal — Mon, 30 Aug 2021 12:30:51 GMT

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.

Re: How to calculate working days

Greg_Deckler — Mon, 30 Aug 2021 12:47:39 GMT

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

Re: How to calculate working days

venal — Mon, 30 Aug 2021 12:58:26 GMT

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

Re: How to calculate working days

VahidDM — Mon, 30 Aug 2021 13:27:45 GMT

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

Re: How to calculate working days

CNENFRNL — Tue, 31 Aug 2021 10:21:35 GMT

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

Re: How to calculate working days

venal — Thu, 16 Sep 2021 01:58:47 GMT

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