Building Excel WORKDAY-Function in Power BI

[lk94]

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

[Vera_33]

Hi @lk94 

This is a custom function in M

(StartDate as data, EndDate as date, optional Holiday as table) as number =>

let

    ListOfHolidays = if Holiday = null then {} else Table.Column(Holiday, "Date"), // put your own column name

    NumListOfHolidays = 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 null 

        else if StartDate > EndDate then (List.Count(ListSel)-1)*(-1)

        else List.Count(ListSel) -1

in

    Result      

[Greg_Deckler]

@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/38490

[amitchandak · Accepted solution]

@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=kRACuS4eKWA

Or refer 2nd page of this file

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0