CODEWITHSUNDEEP
excelexcel-formulapowerbiformuladax
Albert
Albert

Reputation: 3

Finding Next Business Day with DAX

=IF(AND(WEEKDAY(AA3,2)<5,(AA3-INT(AA3))<17/24),((INT(AA3)+1)+12/24),IF(AND(WEEKDAY(AA3,2)<5,(AA3-INT(AA3))>17/24),((INT(AA3)+2)+12/24),IF(WEEKDAY(AA3,2)=5,(INT(AA3)+4)+12/24,IF(WEEKDAY(AA3,2)=7,(INT(AA3)+2)+12/24,IF(WEEKDAY(AA3,2)=6,(INT(AA3)+3)+12/24,)))))

I am trying to find the next business day depending on day of the week and hour of the day. Here is what I have converted to DAX but it does not work and I have no idea why.

NBD = 
IF (
    AND (
        WEEKDAY ( D2S[Actual Received Time], 2 <= 5 ),
        HOUR ( D2S[Actual Received Time] ) < 14
    ),
    (
        INT ( D2S[Actual Received Time] ) + 23.99 / 24
    ),
    IF (
        AND (
            WEEKDAY ( D2S[Actual Received Time], 2 ) = 5,
            HOUR ( D2S[Actual Received Time] > 14 )
        ),
        (
            INT ( D2S[Actual Received Time] ) + 3 + 12 / 24
        ),
        IF (
            AND (
                WEEKDAY ( D2S[Actual Received Time], 2 ) <= 5,
                HOUR ( D2S[Actual Received Time] ) >= 14
            ),
            INT ( D2S[Actual Received Time] ) + 1 + 12 / 24,
            IF (
                WEEKDAY ( D2S[Actual Received Time], 2 ) = 6,
                INT ( D2S[Actual Received Time] ) + 2 + 12 / 24,
                IF (
                    WEEKDAY ( D2S[Actual Received Time], 2 ) = 7,
                    INT ( D2S[Actual Received Time] ) + 1 + 12 / 24
                )
            )
        )
    )
)

Upvotes: 0

Views: 392

Answers (1)

fstorino
fstorino

Reputation: 36

Line 4 of your formula should read:

        WEEKDAY ( D2S[Actual Received Time], 2 ) <= 5,

(with "<= 5" outside the parenthesis)

Upvotes: 1

Related Questions