Calculating number of business day excluding Country Holidays

[PLM2021]

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

[v-chenwuz-msft · Accepted solution]

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.

[Gabiiiii]

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.

[PLM2021]

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!

[CNENFRNL]

#"list of Holidays per country"[Date]

as Holidays parameter to be passed into the function.

[PLM2021]

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?

[Nathaniel_C]

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

[PLM2021]

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