topic Re: Calculating number of business day excluding Country Holidays in DAX Commands and Tips

Calculating number of business day excluding Country Holidays

PLM2021 — Fri, 19 Nov 2021 20:45:34 GMT

Hi,

I am trying to figure out how to calculate the number of business days (Age of Order) between the date an order was submitted and today (using a table with list of Holidays per country)

Here are my tables:

COUNTRY TABLE

HOLIDAYS TABLE

ORDER TABLE

I am able to add a column by invoking the following custom function – this will exclude week-ends and also any dates in the Holidays table.

= (StartDate as date, Holidays as list) => let EndDate = DateTime.Date(DateTime.LocalNow()), // Providing for logic where EndDate is after StartDate Start = List.Min({StartDate, EndDate}), End = List.Max({StartDate, EndDate}), // Switch sign if EndDate is before StartDate Sign = if EndDate < StartDate then -1 else 1, // Get list of dates between Start- and EndDate ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)), // if the optional Holidays parameter is used: Keep only those dates in the list that don t occur in the list of Holidays; // otherwise continue with previous table DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )), // Select only the first 5 days of the week // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, 1) < 5 ), // Count the number of days (items in the list) CountDays = (List.Count(DeleteWeekends) * Sign) - 1 in CountDays

The piece that I can’t seem to figure out is how to only apply holidays matching the country of the order.

Thanks for you help

Re: Calculating number of business day excluding Country Holidays

Nathaniel_C — Sat, 20 Nov 2021 00:44:48 GMT

Hi PLM2021
Please post your pbix.

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you.

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel

Re: Calculating number of business day excluding Country Holidays

CNENFRNL — Sat, 20 Nov 2021 14:28:07 GMT

#"list of Holidays per country"[Date]

as Holidays parameter to be passed into the function.

Re: Calculating number of business day excluding Country Holidays

PLM2021 — Mon, 22 Nov 2021 17:19:53 GMT

Nam - Thank you for the reply. Here is an example of .pbix file
https://drive.google.com/file/d/1btNDeG6XkVX2SpXUdFKIRg71zmB998Wk/view?usp=drivesdk

In the example, the function will exclude weekends and also any dates in the Holiday tables. The end result I wish to achieve is to only calculate Holidays based on the order country.

Re: Calculating number of business day excluding Country Holidays

PLM2021 — Mon, 22 Nov 2021 17:18:36 GMT

Thank you for your reply CNENFRNL .

Holidays parameter does get pass to the function as a list and is being apply to any order. The piece that I can't figure out is how to only apply the Holidays to the matching countries of the order.

I tried passing Holidays as a table rather then a list. This way both columns will be accessible through the function but I am struggling understanding the syntax to parse and compare the country to only apply Holidays for matching order countries. Is it possible to achieve in a single function?

Re: Calculating number of business day excluding Country Holidays

v-chenwuz-msft — Wed, 24 Nov 2021 08:18:31 GMT

Hi PLM2021

You can use the Table.SelectRows to filter the holidays depend on the CountryID.

Please refer the following code.

let Source = (_Countryid as text , _Holidays as table, StartDate as date) => let MatchHolidaysTalbe = Table.SelectRows(_Holidays,each [CountryID] = _Countryid), MathcHolidaysColumn = Table.ToColumns(MatchHolidaysTalbe), MathcHolidaysList =MathcHolidaysColumn{0}, EndDate = DateTime.Date(DateTime.LocalNow()), // Providing for logic where EndDate is after StartDate Start = List.Min({StartDate, EndDate}), End = List.Max({StartDate, EndDate}), // Switch sign if EndDate is before StartDate Sign = if EndDate < StartDate then -1 else 1, // Get list of dates between Start- and EndDate ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)), // if the optional Holidays parameter is used: Keep only those dates in the list that don t occur in the list of Holidays; // otherwise continue with previous table DeleteHolidays = if MathcHolidaysList = null then ListOfDates else List.Difference(ListOfDates, List.Transform(MathcHolidaysList, Date.From )), // Select only the first 5 days of the week // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, 1) < 5 ), // Count the number of days (items in the list) CountDays = (List.Count(DeleteWeekends) * Sign) - 1 in CountDays in Source

In fact, the M function is not a good choice for analyzing data, I recommend DAX.

AgeOfOrder_DAX = VAR _END = TODAY() VAR _START = [SubmittedOn].[Date] VAR _totalday = GENERATESERIES( _START, _END ) VAR _1 = GROUPBY( FILTER( 'Holidays', [CountryID] = EARLIER( 'Order'[CountryID] ) ), [Date] ) VAR _2 = EXCEPT( _totalday, _1 ) VAR _a = ADDCOLUMNS( _2, "weeknum", WEEKDAY( [Value], 2 ) ) RETURN COUNTROWS( FILTER( _a, [weeknum] < 6 ) ) - 1

I put the pbix file in the end you can refer

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculating number of business day excluding Country Holidays

PLM2021 — Wed, 24 Nov 2021 15:05:32 GMT

Hi v-chenwuz-msft ,

Thank you for your post. This is exactly what I was looking for.... And I think that it's more appropriate to perform this operation at the dataset level rather then the dataflow.

Cheers!

Re: Calculating number of business day excluding Country Holidays

Gabiiiii — Thu, 30 Jun 2022 06:21:32 GMT

Hi chenwuz,
This is great! But instead of business days, how can we calculate it at hour level (business hour) using this DAX sample. I tried to modified it, but it didn't work out.