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>
 

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
211
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

Board Regular
Joined
Apr 30, 2014
Messages
247
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]
        )
    )
 

Forum statistics

Threads
1,078,346
Messages
5,339,677
Members
399,318
Latest member
kryten68

Some videos you may like

This Week's Hot Topics

Top