Forum Discussion

lk94's avatar
lk94
Regular Visitor
6 years ago
Solved

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. ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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