Return lowest date across two tables

SJLoxton

New Member
Joined
Oct 21, 2019
Messages
1
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
Membership Table Member Table Result I want
Member IDEnd Date Member IDDeceased Date Member IDDate
121/01/2020 131/12/2019 131/12/2019
213/05/2000 2 213/05/2000
31/01/2019 3 31/01/2019
431/12/2018 41/05/2019 431/12/2018
55/04/2020 5 55/04/2020

<colgroup><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>
</tbody>
Appreciate your help!
SJ

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
Assuming you want the column in the Membership Table...
Code:
[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]
        )
    )
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top