Forum Discussion

Eagles83's avatar
Eagles83
New Member
9 years ago
Solved

WORKDAY formula in Power BI

I want to count 3 business days from [dateX]   The result must exclude weekends and federal holidays. The Excel equivalent would be WORKDAY(dateX,3,'Federal Holiday Sheet'!A;A))  with the Federal Holiday Sheet listing all holiday dates in column A. So if dateX is 6/27/17 I want the result to be 6/30/17; if dateX is 6/30/17, I wante the result to be 6/6/17 (skip weekend and July4th).

  • Anonymous's avatar
    Anonymous
    9 years ago

    Eagles83,

    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,

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Eagles83,

    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,

     

    • Eagles83's avatar
      Eagles83
      New Member

      I get an error at Step 3 saying Expression.Error: The name 'WEEKDAY' wasn't recognized. Make sure it's spelled correctly. 

       

      Is WeekDay a dax formula or do I need to add a WeekDay table?

       

    • Eagles83's avatar
      Eagles83
      New Member

      This was very helpful. Can you please clarify which 'Date' in your response represents my "DateX" in my question? I'm not sure if my DateX is the Date if the Fact Table for the Date in the Calendar table.

      • Anonymous's avatar
        Anonymous
        Not applicable

        It's possible to calculate workdays without a Date table with the following Measure or Calculated Column in DAX:

         

         
        Workdays Calculated =
            //the work days don't consider Saturdays and Sundays

            //calculate the number of days between the dates, and adds 1
            VAR numDays = DATEDIFF([Start Date], [End Date], DAY) + 1
            //verifies the week numbers of each date
            VAR weekNumStart = WEEKNUM([Start Date])
            VAR weekNumEnd = WEEKNUM([End Date])
            //calculates the number of weekends existing between dates
            VAR numWeekends = IF(
                weekNumStart <= weekNumEnd, //weeks in same year
                weekNumEnd - weekNumStart, //the difference is the number of weekends
                weekNumEnd - weekNumStart + 52 //if different years, adds the number of weeks in one year
            )
        RETURN
            numDays - (numWeekends * 2) //calculates the number of days (*2 to remove Saturday and Sunday for each weekend)
  • Anonymous's avatar
    Anonymous
    Not applicable

    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
      FnWorkingDays

    My 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 

    • tommuirwebb's avatar
      tommuirwebb
      Frequent Visitor

      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.

       

       

  • The simplest way I found to calculate this without considering holidays (only weekends) is to use a SWITCH function on WEEKDAY of the date:

     

     

    [Date] + SWITCH(WEEKDAY([Date]), 1, 3, 2, 3, 3, 3, 4, 5, 5, 5, 6, 5, 7, 4)

     

     

    What this does is:

     

    • Sun → Wed (+3)
    • Mon → Thur (+3)
    • Tue → Fri (+3)
    • Wed → Next Mon (+5)
    • Thur → Next Tue (+5)
    • Fri → Next Wed (+5)
    • Sat → Next Wed (+4)