Expressions that yield variant data-type cannot be used to define calculated columns.

[Tad17]

Hey Guys

BIZ DATE = if(and(Format([ATD],"")=blank(),format([BIZ DAYS],"")=blank()),"",

var dateidx = calculate( max('Calendar'[WorkingDayIndex] ) , 'P FILES'[ATD] = RELATED('Calendar'[Date] ) )

var newdateidx = dateidx + 'P FILES'[BIZ DAYS]

return

calculate(

max('Calendar'[Date]),

filter(

ALL('Calendar'),

'Calendar'[WorkingDayIndex] = newdateidx

&& 'Calendar'[IsWorkDay] = 1

)

)

)

Data is company sensetive.

I found a forum that suggested creating a calendar table to add days to a date and that seems to work (everything after the first line). However, the if statement will not seem to work. I understand that I am looking at two columns with different formatting (one is date and the other is text) I have tried using several combinations of value and format functions to no avail. I cannot change the formatting of these columns as their formatting is necessary for other calculated columns. I have tried making helper columns a little, but haven't had much luck.

Also, the ATD column, which is date format, has blanks (null) in it.

Any suggestions are appreciated.

-Tad

[sturlaws]

Hi @Tad17,

what is your desired outcome when 'P FILES'[ATD] and 'P FILES'[BIZ DAYS] are both blank? Because as it is written now, dateidx does not get a value when they are both blank.

You could try something like this

BIZ DATE =
VAR dateidx =
    IF (
        AND ( ISBLANK ( [ATD] ), ISBLANK ( [BIZ DAYS] ) ),
        BLANK (),
        CALCULATE (
            MAX ( 'Calendar'[WorkingDayIndex] ),
            'P FILES'[ATD] = RELATED ( 'Calendar'[Date] )
        )
    )
VAR newdateidx = dateidx + 'P FILES'[BIZ DAYS]
RETURN
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[WorkingDayIndex] = newdateidx
                && 'Calendar'[IsWorkDay] = 1
        )
    )