Variable in Calculated column not taking a value from nonrelated table

[LP280388]

Hi Team,

I have the below Measure which results in Yes/No based on the condtions given.  Due to some reason I need to change this measure to a Calculated column so that it can be used in other measure calculations.  but i get a error "cant find name" for the Variables FirstDay and LastDay.   

Please help how to make this as a calculated column.



column = VAR FirstDay = CALCULATE(

MIN('From Date'[Date]),

ALLSELECTED('From Date'[Date]))

VAR LastDay = CALCULATE(

MAX('To Date'[Date]),

ALLSELECTED('To Date'[Date]))

Return

SWITCH(TRUE(),

MAX(VPR[AStartDate]) > LastDay, "No",

MAX(VPR[AStartDate]) >= FirstDay && Max(VPR[AEndDate]) <= LastDay,"Yes",

Max(VPR[AStartDate]) <= FirstDay && Max(VPR[AEndDate]) = BLANK(),"Yes",

MAX(VPR[AStartDate]) <= FirstDay && Max(VPR[AEndDate]) <= FirstDay,"No",

Max(VPR[AEndDate]) <= LastDay,"Yes",

MAX(VPR[AStartDate]) <= FirstDay && Max(VPR[AEndDate]) >= LastDay,"Yes",

Max(VPR[AEndDate]) = BLANK(),"Yes", "")

[v-jayw-msft · Accepted solution]

Hi @LP280388

Try adding a filter() to the formula.

VAR FirstDay =
    CALCULATE (
        MIN ( 'From Date'[Date] ),
        FILTER ( ALLSELECTED ( 'From Date'[Date] ), 1 = 1 )
    )
VAR LastDay =
    CALCULATE (
        MAX ( 'To Date'[Date] ),
        FILTER ( ALLSELECTED ( 'From Date'[Date] ), 1 = 1 )
    )

Best Regards,

Jay

[LP280388]

Thank you very much.