How do I ....

[RonaldvdH]

Hey guys,

I'm working on a formula that has multiple IF statements to determine a specific date but I cant get it to work.

Basically I need one date (for each row) on which a certain activity will be planned.

Activity A is the exact date so when that is filled it should use that but if that is not filled (the adres hasn't been planned yet) it should calculated base on the status of Activity B, C and D.

However the outcome of that calculated date could mean that the date is < TODAY and then it should calculate TODAY() + xx (if not it should use the outcome of the first calculation

HC estimated +84 = 
IF(Adressen[Activity A]<>BLANK(),Adressen[Activity A],
IF(Adressen[Activity B]<>BLANK()||Adressen[Permission]="YES",
        IF(Adressen[Activity B]+84<TODAY(),TODAY()+60,
            Adressen[Activity B]+84,
IF(Adressen[Activity C]<>BLANK()|| Adressen[Permission]="YES",
        IF(Adressen[Activity C]+120)<TODAY(),TODAY()+80,
            Adressen[Activity C]+120,
IF(Adressen[Activity D]<>BLANK()||Adressen[Permission]="YES",
    IF(Adressen[Activity D]+180)<TODAY(),TODAY()+90,
            Adressen[Activity D]+180
IF(Adressen[Activity D]=BLANK(),
    TODAY()+180)))
)
)

[ERD · Accepted solution]

@RonaldvdH , personally, I'd use switch for such cases, but, anyway, your measure has a lot if syntax errors. I suppose it should be like this:

HC estimated +84 =
IF (
    Adressen[Activity A] <> BLANK (),
    Adressen[Activity A],
    IF (
        Adressen[Activity B] <> BLANK () || Adressen[Permission] = "YES",
        IF (
            Adressen[Activity B] + 84 < TODAY (), TODAY () + 60, Adressen[Activity B] + 84
        ),
        IF (
            Adressen[Activity C] <> BLANK () || Adressen[Permission] = "YES",
            IF (
                Adressen[Activity C] + 120 < TODAY (), TODAY () + 80, Adressen[Activity C] + 120
            ),
            IF (
                Adressen[Activity D] <> BLANK () || Adressen[Permission] = "YES",
                IF (
                    Adressen[Activity D] + 180 < TODAY (), TODAY () + 90, Adressen[Activity D] + 180
                ),
                IF ( Adressen[Activity D] = BLANK (), TODAY () + 180 )
            )
        )
    )
)

[RonaldvdH]

how would the formula work if I would use the SWITCH function ?

If you think that is better I'm open to it

[ERD]

You can play with the function using this article: https://dax.guide/switch/.

Anyway, this is all just syntax sugar, just make sure the syntax is correct.