Find associated row in Fact table

[CRBam]

Hi, I am trying to create a calculated field in my data that will let me know if a policy has been renewed.  In my fact table there is a unique polID, and a unique ParentPolID.  Each term should have a different polID but the same ParentPolID. So what i'd like to do (in english) is;

If this row's (A) ParentPolID exists in another row (B) with an Effective date (B) > this rows expiration date (A) AND that row (B) has a status of Issued, flag this row (A) as renewed.

I have separate date tables for Effective Date and Expiration Date, with both containing Date keys linked to the fact table. 

The report evenutally will let me list the policies that are expirign this month, grouped by what has been rened and what has not been renewed.

Any suggestions would be greatly appreciated!

[v-yuezhe-msft]

@CRBam,

Could you please share sample data of your table that can be copied and pasted to us? And also please post expected result here based on your sample data so that we can provide you appropriate dax.

Regards,
Lydia Zhang

[CRBam · Accepted solution]

I posted a solution to a different problem, my apologies.  I will post that separately so others can find it.  I have decided to address this through the SQL source instead of Power BI. I don't have any caluclations or code because I don't even know where to begin.