Created Column: Return value if date from one table is between two dates in another table

[Anonymous]

Hi,

I need to create a column that will return 'HPP_PlanYears_Crosswalk'[PlanYear] when 'ClaimData'[IncurredFrom] is between 'HPP_PlanYears_Crosswalk'[PlanYearStart] and 'HPP_PlanYears_Crosswalk'[PlanYearEnd] AND where 'HPP_PlanYears_Crosswalk'[GroupID] = 'ClaimData'[GroupID]

'ClaimData'[IncurredFrom], 'HPP_PlanYears_Crosswalk'[PlanYearStart], 'HPP_PlanYears_Crosswalk'[PlanYearEnd] are already formatted as dates.

Can you please assist me?  I found a few example threads posted but none were exactly what I needed.

Thank you,

Andrew

[vanessafvg]

try this?

Plan Year =
VAR groupid =
    SELECTEDVALUE ( 'ClaimData'[GroupID] )
VAR result =
    CALCULATE (
        'HPP_PlanYears_Crosswalk'[PlanYear],
        'ClaimData'[IncurredFrom] >= 'HPP_PlanYears_Crosswalk'[PlanYearStart]
            && 'ClaimData'[IncurredFrom] <= 'HPP_PlanYears_Crosswalk'[PlanYearEnd]
            && 'HPP_PlanYears_Crosswalk'[GroupID] = groupid
    )
RETURN
    result

[Anonymous]

This is close to what I need but I am getting an error with your DAX.  I tried to resolve it but couldn't get your solutuon to work.

The error reads:  The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.

[vanessafvg · Accepted solution]

if you changed the plan  year to max it probably would have worked, i see you changed it, but you have an iterator ie Maxx, do you specifically  need an iterator as they are more performance hungry.

[Anonymous]

This is what I needed:

Corrected_PlanYear =

VAR _Date = ( ClaimData[IncurredFrom] )

VAR _ID = ( ClaimData[GroupID] )

VAR _PY =

MAXX (

FILTER (

PlanYears_Crosswalk,

PlanYears_Crosswalk[GroupID] = _ID &&

_Date >= PlanYears_Crosswalk[PlanYearStart] &&

_Date <= PlanYears_Crosswalk[PlanYearEnd]

),

[PlanYear]

)

RETURN

_PY