Forum Discussion
WORKDAY formula in Power BI
Firstly, import your Federal Holiday sheet and data table to Power BI Desktop.
Secondly, create a calendar table using calendar() function, create relationship between data table and calendar table using date field, and create relationship between Federal Holiday table and calendar table using date field, here is an example for you.
Thirdly, create the following calculated columns in the calendar table.
WeekDay = WEEKDAY('Calendar'[Date])
Holiday = RELATED('Federal Holiday'[Holiday])
If work day = IF(OR('Calendar'[WeekDay]=1,'Calendar'[WeekDay]=7),0,IF(ISBLANK('Calendar'[Holiday]),1,0))
Rank = RANKX(FILTER('Calendar','Calendar'[If work day]=1),'Calendar'[Date],,ASC)
Add 3 businss days = LOOKUPVALUE('Calendar'[Date],'Calendar'[If work day],1,'Calendar'[Rank],'Calendar'[Rank]+3)At last, create a calculated column using the following DAX in your data table.
Column = RELATED('Calendar'[Add 3 businss days])
Regards,
I have been looking for a Formular equal to Excels <=WORKDAY(start_date, days, [holidays])>, maybe that would be the Solution:
// FnWorkingDays
/*
@Startdate type DATE
@NumOfDays type INT // negative counts back
@HDays type Table,
*/
let FnWorkingDays = (StartDate as date, NumOfDays as number, optional Holidays as table) as date =>
let
// get Holidays from Table
ListOfHolidays = if Holidays = null then {} else Table.Column(Holidays,"ColumnNameFromHolydayTable"),
// Convert Dates into Numbers for Quiker Search
NumListOfHolidays = List.Transform(ListOfHolidays ,each Number.From(_)),
// Define the Direction of Count, Negativ: Backwards
AddDayDirection = if NumOfDays<0 then -1 else 1,
// make NumOfDays Absolute for Count
NumOfDaysAbs = Number.Abs(NumOfDays),
// generate a Datelist with to many dates
GenerateListDates = List.Dates( StartDate, NumOfDaysAbs*3, #duration(1*AddDayDirection,0,0,0)),
// Select all none Weendend dates
ListDatesNoWeekend = List.Select(GenerateListDates,(_)=>Date.DayOfWeek(_, Day.Monday) < 5),
// Select all none Holydaydates
ListDatesNoHoliday = List.Select(ListDatesNoWeekend,(_)=>List.PositionOf(NumListOfHolidays,Number.From(_))=-1),
// Pick the NumOfDays + 1 from list
ListDates = List.LastN(List.FirstN(ListDatesNoHoliday,NumOfDaysAbs+1),1),
// Output last Date
outputDate = ListDates{0}
in
outputDate
in
FnWorkingDaysMy first attempt was to Build the Func. with List.Generate(), couldn´t to work Proberly.
I expect this Function has a Overhead Problem with to Many Records, therefor please comment
Hi Steven, this function works well unless the StartDate is a Monday and you attempt to subtract 1 business day from it - it then just returns the StartDate value instead of the last business day.