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>
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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

Active Member
Joined
Apr 30, 2014
Messages
257
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,089,443
Messages
5,408,264
Members
403,194
Latest member
UnknownQ

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top