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 Hol...
Need Help
  • 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,

     

Anonymous's avatar
Anonymous
Not applicable
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,

 

  • Eagles83's avatar
    Eagles83
    New Member
    8 years ago

    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
    8 years ago

    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
      7 years ago

      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)