how to incorporate IF statement to existing DAX formula?

[Anonymous]

Hello Community,

I have two columns, both of which are date w/timestamp.  The column titled "Field Recipient Assigned Date" is the date the file was created and the column titled "Field Assigned Time Stamp" is the date the file was reviewed.  I already have a DAX formula that calculates the amount of days/time that has lapsed between the reviewed and created dates.  The problem I am running into is that I am getting an error message stating "The start date or end date in Calendar function can not be Blank value".  My assumption is that this error is occurring because some of the cells are blank (this means a file has not yet been reviewed).  Assuming my assumption is correct, how would an IF statement be incorporated to return the value "Not Reviewed" if the cell is blank, otherwise return the calculation?  I would appreciate any help I can get!

Here's the DAX formula I have so far

Column = VAR Calendar1 = CALENDAR([Field Recipient Assigned Date],[Field Assigned Time Stamp])

VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))

RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date]) & " Days " & HOUR(MOD([Field Assigned Time Stamp]-[Field Recipient Assigned Date],1)) & " Hours " & MINUTE(MOD([Field Assigned Time Stamp]-[Field Recipient Assigned Date],1)) & " Minutes"

  

[v-xjiin-msft]

Hi @Anonymous,

Did you mean this?

Column =
VAR Calendar1 =
    CALENDAR (
        [Field Recipient Assigned Date],
        IF (
            HASONEVALUE ( Table3[Field Assigned Time Stamp] ),
            Table3[Field Assigned Time Stamp],
            Table3[Field Recipient Assigned Date]
        )
    )
VAR Calendar2 =
    ADDCOLUMNS ( Calendar1, "WeekDay", WEEKDAY ( [Date], 2 ) )
RETURN
    COUNTX ( FILTER ( Calendar2, [WeekDay] < 6 ), [Date] )
        & " Days "
        & HOUR (
            MOD ( [Field Assigned Time Stamp] - [Field Recipient Assigned Date], 1 )
        )
        & " Hours "
        & MINUTE (
            MOD ( [Field Assigned Time Stamp] - [Field Recipient Assigned Date], 1 )
        )
        & " Minutes"

Thanks,
Xi Jin.

[Anonymous]

Hi @v-xjiin-msft

First off, thank you very much!!  I no longer receive an error message with your formula and everything calculates correctly.  However, the formula is somehow making calculations even when the "reviewed" ("Field Assigned Time Stamp" column) date is missing.  How is this possible?  Is there a way for the result to say something like "Not Reviewed" IF the date is missing?

[v-xjiin-msft]

Hi @Anonymous,

Sorry for the delay.

Check this:

Column =
VAR Calendar1 =
    CALENDAR (
        [Field Recipient Assigned Date],
        IF (
            ISBLANK ( Table5[Field Assigned Time Stamp] ),
            [Field Recipient Assigned Date],
            Table5[Field Assigned Time Stamp]
        )
    )
VAR Calendar2 =
    ADDCOLUMNS ( Calendar1, "WeekDay", WEEKDAY ( [Date], 2 ) )
RETURN
    IF (
        ISBLANK ( Table5[Field Assigned Time Stamp] ),
        "Not Reviewed",
        COUNTX ( FILTER ( Calendar2, [WeekDay] < 6 ), [Date] )
            & " Days "
            & HOUR (
                MOD ( [Field Assigned Time Stamp] - [Field Recipient Assigned Date], 1 )
            )
            & " Hours "
            & MINUTE (
                MOD ( [Field Assigned Time Stamp] - [Field Recipient Assigned Date], 1 )
            )
            & " Minutes"
    )

Thanks,
Xi Jin.

[Anonymous]

Hi @v-xjiin-msft,

No need to apologize.  I'm just grateful for your help.  For some reason the formula does not work on my file.  I copy/pasted exactly what you have and I receive an error message.

[v-xjiin-msft]

Hi @Anonymous,

Since you are using this expression CALENDAR([Field Recipient Assigned Date],[Field Assigned Time Stamp]) to create a calendar table. You should verify that the [Field Assigned Time Stamp] will always bigger than [Field Recipient Assigned Date].

That's what the error means.

Thanks,
Xi Jin.

[Anonymous]

Hi @v-xjiin-msft,

Thats what I assumed but just wanted to make sure.  Whats strange is that I double checked to make sure the date is AFTER the created date, but its still giving me the error.  I'll review some more.  Thank you.