Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

To add new column, by comparing date fields from 2 tables

Hello, I have just started with Power BI, I'm not sure if this is a lame question to ask.   I have start_date in Table A and Approval_date in Table B.I want to compare these 2 date columns and cre...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Anonymous ,

     

    If there is a primary key,you could use below method:

    Create a column as below:

    approval date = LOOKUPVALUE('Table B'[Approval_date],'Table B'[Index],'Table'[Index],blank())

    And you will see:

    Then you create another column to compare the two dates.

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my raeply as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

No relationship--that's pretty tough to work with, but if you know that the dates between tables are on the same row, then you could try add a step like:

= List.Zip({#"Table A"[Start_Date], #"Table_B"[End_Date]})

 

Then you can extract the list values (as opposed to expanding to new rows) and do the row by row math.

 

--Nate

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous I will try to get the relationship from the source team.Lets think we have key(PK) how can we compare.

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi  Anonymous ,

       

      If there is a primary key,you could use below method:

      Create a column as below:

      approval date = LOOKUPVALUE('Table B'[Approval_date],'Table B'[Index],'Table'[Index],blank())

      And you will see:

      Then you create another column to compare the two dates.

      For the related .pbix file,pls see attached.

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my raeply as a solution!