Problems with sum of business days

[Labraham36]

Hello Community!

I have a question: On the one hand I have the data of a date. On the other hand, I have a number of days, and what I need is to obtain the day that results from adding that date to that number of days. The problem with this is that that number of days correspond to business days, so in that sum, neither Saturdays, nor Sundays, nor holidays should be taken into account. I have a calendar table with all the possible dates and a field that informs if that date is a business day (weekday not a holiday) with a "True", or if it is a non-business day (Saturday, Sunday or holiday) How could I do to solve this problem?

Thank you very much!

[Syndicate_Admin]

I have the same problem, I tried the code and it works for some dates, others bring me "True". Help please.

[v-rongtiep-msft · Accepted solution]

Hi @Labraham36 ,

I have modify the measure. Please refer to it to see if it helps you.

Create a flag column on calendr table.

flag =
VAR _workday =
    WEEKDAY ( 'Table 2'[Date], 2 )
RETURN
    IF ( _workday > 5, FALSE (), TRUE () )

Then create a measure.

Measure_4 =
VAR _plus =
    MAX ( 'Table'[date] ) + MAX ( 'Table'[value] )
VAR _days =
    CALCULATE (
        COUNT ( 'Table 2'[Date] ),
        FILTER (
            ALL ( 'Table 2' ),
            'Table 2'[Date] >= MAX ( 'Table'[date] )
                && 'Table 2'[Date] <= _plus
                && 'Table 2'[flag] = FALSE ()
        )
    )
VAR _retyrnda = _plus + _days
VAR _flagresult =
    CALCULATE (
        SELECTEDVALUE ( 'Table 2'[flag] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[Date] = _retyrnda )
    )
VAR _measure1 = _retyrnda + 1
VAR _res =
    CALCULATE (
        SELECTEDVALUE ( 'Table 2'[flag] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[Date] = _measure1 )
    )
VAR _measure2 = _retyrnda + 2
RETURN
    IF (
        _flagresult = FALSE ()
            && _res = TRUE (),
        _measure1,
        IF (
            _flagresult = FALSE ()
                && _res = FALSE (),
            _measure2,
            IF ( _flagresult = TRUE (), _flagresult, BLANK () )
        )
    )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rongtiep-msft]

Hi @Labraham36 ,

Do you have two tables?
One is a data table and the other is a date table marked with a non-working day of false.

If so, please refer to my pbix file to see if it help you.

Create relationships between the tables.

Then create a measure.

Measure =
VAR _a =
    MAX ( 'Table'[date] ) + MAX ( 'Table'[value] )
RETURN
    CALCULATE ( _a, FILTER ( ALL ( 'Table 2' ), 'Table 2'[Column] = TRUE () ) )

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Labraham36]

Thank you very much for the proposed solution!

I think we are close, but it is not giving correctly. For eg; For January 9, if we add 15 working days, the date that should be returned is January 28, not 24, since the result it is giving is the same as adding the days to the date directly. Surely there must be a small error of logic.

[tom480]

Hi @Labraham36 ,

Without seeing the tables or some data, I think this article will explain it better than I could in a post.  I think this describes exactly what you are looking for.  If the link is beneficial, please mark as a solution to help others find the solution too!  Respectfully, Tom

Counting working days in DAX - SQLBI 

[Labraham36]

Thank you very much for the reply!

It's something similar, but not specifically that. (In fact I have that same development on the board, but I use it for something else). I'll see if I explain myself better:

I have two columns. One with the date the sale occurs, for example, and another column with the number of business days before the expiration of said sale. Ex:

Sale date: 3/1/2022

Expiration business days: 10

Now, what I need with this data is to obtain the expiration date. Logically, if I add the date and the number, the result will be 1/13/2022, but I would not be considering that the days are only business days, and therein lies my problem. The result should actually be 1/17/2022.

I hope I have been more clear. Thank you!