Return lowest date across two tables

[SJLoxton]

Hello- I am new to Power BI & DAX. I am struggling to work out how to return lowest date across two tables.
I want the following rules applied
If deceased date blank then end date
If deceased date before end date- deceased date
All else end date

Format Looks like Below
[TABLE="width: 478"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][TABLE="width: 653"]
<colgroup><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Membership Table[/TD]
[TD] [/TD]
[TD="colspan: 2"]Member Table[/TD]
[TD] [/TD]
[TD="colspan: 2"]Result I want[/TD]
[/TR]
[TR]
[TD]Member ID[/TD]
[TD]End Date[/TD]
[TD] [/TD]
[TD]Member ID[/TD]
[TD]Deceased Date[/TD]
[TD] [/TD]
[TD]Member ID[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]21/01/2020[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/12/2019[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/12/2019[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]13/05/2000[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13/05/2000[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1/01/2019[/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1/01/2019[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]31/12/2018[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1/05/2019[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]31/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5/04/2020[/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5/04/2020[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Appreciate your help!
SJ
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Craigc3814]

I know how to do it through Power Query to get you up and running but someone will have a better answer through DAX.

Merge your queries on member id and expand the deceased date. Create an if formula in power query to get a new column with your answer.

 

[macfuller]

Assuming you want the column in the Membership Table...

[Revised End Date] =VAR DeceaseDate =
    CALCULATE (
        MAX ( 'Member Table'[Deceased Date] ),
        FILTER (
            'Member Table',
            'Member Table'[Member ID] = 'Membership Table'[Member ID]
        )
    )
RETURN
    IF (
        ISBLANK ( DeceaseDate ),
        'Membership Table'[End Date],
        IF (
            'Membership Table'[End Date] - DeceaseDate > 0,
            DeceaseDate,
            'Membership Table'[End Date]
        )
    )