What is wrong with this formula?

[colettb]

HI Community!

I am trying to create a measure that compares 2 dates together to determine whether it should be flagged as a 0 or 1.  

IncludeDate = IF(NOT ISBLANK([EDC Go-Live]),

IF([EDC Go-Live] >= [First Date to Include],

1,

0))

[EDC Go-Live] and [First Date to Include] are both columns in my data.  What I'm trying to do is check if [EDC Go-Live] has  value and if it does, check if it is >= the date in [First Date to Include].  If it is, flag it as 1, and if it's before the [[First Date to Include] date then flag it as 0.

My error message is: "The value for [EDC Go-Live] cannot be determined.  Either the column does not exist or there is no current row for this column".    The column does exist, though not all rows have a value in it.

Does anyone have any ideas on where I went wrong on this?

Thanks!

[v-sgandrathi]

Hi @colettb,

Thank you for your question, and thank you to our community members for their precise guidance.
As mentioned, the issue arises from using row-level column references within a measure, which operates in a filter context and does not evaluate row-by-row. For such logic, comparing values from two columns on the same row a calculated column is the appropriate solution.
Also, if your visuals ensure a single row context, you can use SELECTEDVALUE in a measure. Please try the suggested calculated column formula, and let us know if you need assistance applying it or adapting it to your specific report. If one of the responses resolved your issue,

please mark it as the accepted answer to assist others in the community. We are here to provide further assistance if needed.

Thank you.

[pankajnamekar25]

Hello @colettb 

try this

This fails because column references like [EDC Go-Live] without an aggregation inside a measure don't return a value outside of filter context.

Use this

IncludeDate =

IF(

    NOT ISBLANK('YourTable'[EDC Go-Live]) &&

    'YourTable'[EDC Go-Live] >= 'YourTable'[First Date to Include],

    1,

    0

)

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[colettb]

Thank you!  I appreciate your great explanation!

[Ashish_Excel]

Hi,

This calculated column formula should work

Test = if(and([EDC Go-Live]>0,[EDC Go-Live]>[First Date to Include]),1,0)

Hope this helps.

[Shravan133 · Accepted solution]

you're trying to reference a column in a measure, but you're doing it in a row context-dependent way that doesn't work properly in a measure (which evaluates in filter context).

Use a Calculated Column if you need row-by-row evaluation

IncludeDate =

IF(

    NOT ISBLANK('YourTable'[EDC Go-Live]),

    IF('YourTable'[EDC Go-Live] >= 'YourTable'[First Date to Include], 1, 0),

    BLANK()

)

Use a Measure only if aggregating (e.g., MIN, MAX, FIRSTDATE)

IncludeDateMeasure =

VAR EDCDate = SELECTEDVALUE('YourTable'[EDC Go-Live])

VAR FirstDate = SELECTEDVALUE('YourTable'[First Date to Include])

RETURN

IF(

    NOT ISBLANK(EDCDate),

    IF(EDCDate >= FirstDate, 1, 0),

    BLANK()

)

This works only if a single value is in context (e.g., when filtering by row in a visual or drillthrough).