Two table comparison based on date

[Sertan_CPH]

In product development we keep bill of materials (BOM) with cost information. I upload the current BOM every week. These BOMs are aggrageted in a querry wich returns a table of BOMs appended together with a date column. So one can see the cost of a particular BOM at a certain date. Below shows part of the table.

What I want to achieve is to select two particular dates and see the changes in column 'functions', such electrical (EL), mechanical (ME), packaging (PKG). The purpose is to see what was the assumption for PKG back then and what is it now. I need a table that returns only those items that are different in that particular category.  It is all about change traking. Because cost increases or decreases in a development happens, but taking necessary actions is the key. Without knowing what is causing the change, one can't tackle the problem. Can anyone help me with how to go about this issue? 

[v-yetao1-msft · Accepted solution]

Hi @Sertan_CPH 

You can create relationships between two tables based on date ,and then detect whether the status for column 'functions' is changed .

Or you can use TREATAS() dax to transport filters from (unrelated) tables .

The usage for TREATAS() dax can refer to the link :

https://docs.microsoft.com/en-us/dax/treatas-function

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

On a simple dataset (which can be pasted in MS Excel), show the expected result.

[lbendlin]

I recommend you look at the TREATAS() function, it allows you to transport filters from (unrelated) tables, but it also allows you to do sliding window comparisons in the same table.