Results 1 to 3 of 3

Thread: Return lowest date across two tables
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return lowest date across two tables

    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 ID End Date Member ID Deceased Date Member ID Date
    1 21/01/2020 1 31/12/2019 1 31/12/2019
    2 13/05/2000 2 2 13/05/2000
    3 1/01/2019 3 3 1/01/2019
    4 31/12/2018 4 1/05/2019 4 31/12/2018
    5 5/04/2020 5 5 5/04/2020
    Appreciate your help!
    SJ

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    211
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return lowest date across two tables

    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.

  3. #3
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    234
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return lowest date across two tables

    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]
            )
        )

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •