Compare dates between two related tables via a calculated column

[queryuser]

Hello Everyone!

Your advice Power BI  is needed. I am struggling to create a new column (green) that would indicate if the dates have changed between Old Date & New Date irrespective of the timestamp (snapshot of data). Tables have a Many to Many relationship based on Key (Document Nr.)

Merging them is not an option because left table is incrementally refreshed containing historical & today's timestamp of data while table on the right only today's timestamp (snaphot) of data.

The result shall be a new column saying if dates:

Did not change, moved within month, moved to next or previous month.

And if no document nr. matches (missing in left table (for timestamps before today) but present in the right one) then new order

Many thanks,

[queryuser · Accepted solution]

Hi I found the solution, in the end it was the M Query. The reason for not being able to merge the tables was that Table A & Table B contained the same data for Today. So to solve the issue I did the following:

1. Add a custom column to Table A saying (if [TimeStamp] = Date.From(DateTime.LocalNow()) then "Today" else [TimeStamp]

2. Filtered out Today from the Table A

3. Merged table A Key with table B Key

4. Got new dates 

5. Added a column and added the rules based on date differences

[amitchandak]

@queryuser , a new column in table B

maxx(filter(TableA, TableB[Document Nr] = TableA[Document Nr] &&   TableB[timestamp] = TableA[timestamp] ), TableA[Old date])

[queryuser]

Hi @amitchandak ,

Thank you for the input. Would say that the new column shall be in Table A since it is a table with historical data. Doing it for Table B would not lead to the desired result.

Just tried it for Table B and it brings up blank values - for the Historical data from Table B
After reversing the formula for Table A it seems to not work anyomere