lookup column value from related table

[aghanchi]

How can I get the Dates[FY] column value based on the inactive relationship between the Cases[CloseDate] and Dates[Date] columns?

I want to add a column to Cases table that will hold this value.

[joglidden2]

Amit-Chandak, you always deliver! @amitchandak 

[aghanchi · Accepted solution]

Found the Solution:

CloseDateFY = LOOKUPVALUE(Dates[FY], Dates[Date], COALESCE('Cases'[CloseDate], TODAY()))

[amitchandak]

@aghanchi , not needed having an active relationship. Example of measures 

calculate(countrows(table), USERELATIONSHIP('Date'[date], Table[CloseDate]))

calculate(countrows(table), USERELATIONSHIP ('Date'[date], Table[OpenDate]))

[aghanchi]

@amitchandak I'm sorry, I forgot to mention that I am not needing an aggregation. Rather I need to add a column to the Cases table that is fetched from the Dates table based on that inactive relationship i.e. Cases[CloseDate] = Dates[Date].

[amitchandak]

@aghanchi , refer my blog on similar topic can you help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[aghanchi]

I'm sorry @amitchandak . The article did not apply to my situation. I have now rephrased my question. Please see it again. I need a column value from the Dates table based on an inactive relationship. I know I will be using USERELATIONSHIP somewhere in there, but don't know how.

Thanks.