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,

     

Funk-E-Guy's avatar
Funk-E-Guy
Helper II
2 years ago

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)