0

How to calculate the business day of the month

I need to be able to calculate the business day of the month. I have tried the following formula:

Date_Time.Date.workday_seq - lookup(
    Date_Time.Date.workday_seq,
    Date_Time.Date.Date,
    $currentMonthStart
) + 1

This works if there are no holidays between the beginning of the month and the current date. This month, however, Memorial Day (US) causes the count to go off by 1 after 5/25. For example, today, 5/29 should be the 20th business day. The formula evaluates to 21st.

How can I adjust for intermediary holidays between the first of the month and the current date? (I am sure Anurag Malik has some ideas!)

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Looking at the date data, I notice the following:

    Work Day Seq, Date, Day Name

    11,538, 5/23/20, Saturday
    11,538, 5/24/20, Sunday
    11,539, 5/25/20, Monday <-- should this Work Day Seq have been increased?
    11,540, 5/26/20, Tuesday

    Like
  • Hi Marc,

    This Video shows how to  calculate number of business days in a month using Date dimension:

    https://youtu.be/UZGWjoHQCvA

    Like
    • Anurag Malik Thank you, that is a start.  The base requirement is to get a business week ago equivalent to weekAgo for any given date.

      Like
  • Hi Marc, Are you looking for week ago business date?

    •    7 business days ago from today for example

    One way to solve this is create a new table as shown below and join this with date table:

    Like
    • Anurag Malik yes, that is what I am doing now. I have started a csv that contains that plus other meta-dates associated with each date.

      Also, on a related note, I was starting with Date_US.csv as a base and noticed many rows of bad data starting around row 18631. I have corrected my copy, but it took a few hours to get it all right.

      Like
Like Follow
  • Status Answered
  • 6 mths agoLast active
  • 5Replies
  • 39Views
  • 2 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!