Forum Discussion
Building Excel WORKDAY-Function in Power BI
Hi everyone,
I need a function in PowerBI, which provides the same features like the WORKDAY-function in excel. So I want to add several days to a date in consideration of weekends and holidays.
The holidays are in a seperate table. The days, which should be added, are not same for every line and depends on a value, given in the respective line. Also the date, i want to add these days, are given in the respective line.
Thank you for your help. Kind
lk94 , refer if these can help
https://community.powerbi.com/t5/Desktop/WORKDAY-formula-in-Power-BI/td-p/202383
https://community.powerbi.com/t5/Desktop/Number-of-working-days/td-p/22842
https://www.youtube.com/watch?v=kRACuS4eKWAOr refer 2nd page of this file
https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
3 Replies
lk94 , refer if these can help
https://community.powerbi.com/t5/Desktop/WORKDAY-formula-in-Power-BI/td-p/202383
https://community.powerbi.com/t5/Desktop/Number-of-working-days/td-p/22842
https://www.youtube.com/watch?v=kRACuS4eKWAOr refer 2nd page of this file
https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
lk94 - Check out Net Work Days - https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362
Also, Excel to DAX Translation - https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991
Finally since I see you are a New Member some additional useful links:
https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hi lk94
This is a custom function in M
(StartDate as data, EndDate as date, optional Holiday as table) as number =>letListOfHolidays = if Holiday = null then {} else Table.Column(Holiday, "Date"), // put your own column nameNumListOfHolidays = List.Transform(ListOfHolidays, each Number.From(_)),ListOfDays = if StartDate > EndDate then {Number.From(EndDate)..Number.From(StartDate)} else {Number.From(StartDate)..Number.From(EndDate)},ListDiff = List.Difference(ListOfDays,ListOfHolidays),ListSel = List.Select(List.Transform(ListDiff, each Date.DayOfWeek(Date.From(_), Day.Saturday)), each _ >1),Result = if StartDate = null or EndDate = null then nullelse if StartDate > EndDate then (List.Count(ListSel)-1)*(-1)else List.Count(ListSel) -1inResult
