PowerBi calculating date only if another column has a value DAX

Question

Trying to utilize powerbi to help me calculate a date. Currently it is calculating properly if the column "spend_start_Result" has a value, but if this column has no value it is still calculating and giving me a date of 12/30/1899. Is there something wrong with my DAX code?

Arch_review_Calc =
IF (
    Projects[spend_start_Result] = BLANK (),
    0,
    IF (
        Projects[Complexity] = "Low",
        Projects[spend_start_Result] - 45,
        IF (
            Projects[Complexity] = "Medium",
            Projects[spend_start_Result] - 60,
            IF ( Projects[Complexity] = "High", Projects[spend_start_Result] - 90, 0 )
        )
    )
)

I would like the Arch_review_calc column to be blank in that row if the spend_start_result column is blank in that row. Instead it is still calculating and I am unsure where I'm going wrong.

RADO RADO · Accepted Answer · 2018-06-12T18:33:10

Your code replaces blanks with zeros, which are formatted as dates. To avoid that, instead of zeros use BLANK() function.

I would re-write your formula as follows:

   Arch_review_Calc =
    IF (
        ISBLANK ( Projects[spend_start_Result] ), BLANK (),
        SWITCH (
            TRUE,
            Projects[Complexity] = "Low", Projects[spend_start_Result] - 45,
            Projects[Complexity] = "Medium", Projects[spend_start_Result] - 60,
            Projects[Complexity] = "High", Projects[spend_start_Result] - 90,
            BLANK ()
        )
    )

I am not sure about the last blank (inside the SWITCH statement) - if you want 0 instead of blank, replace BLANK() with 0.

PowerBi calculating date only if another column has a value DAX

1 Answers