The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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.
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.
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!
#"list of Holidays per country"[Date]
as Holidays parameter to be passed into the function.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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?
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
Proud to be a Super User!
@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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
32 | |
20 | |
16 | |
16 |
User | Count |
---|---|
56 | |
51 | |
36 | |
35 | |
31 |