Subtract values of columns from two different tables based on date

[Anonymous]

Hi all!

I've been struggling with this issue and I haven't found a solution by myself. I hope I will find it here: I want to know if it is possible to get a measure of the difference of two values in different tables with the same date. 

As an example, these are my two tables:

Table A:

Date	Product	Value
25/03/2022	A	3.23
26/03/2022	A	3.39
25/03/2022	B	4.12
26/03/2022	B	4.29

Table B:

Date	Product	Value
25/03/2022	A1	3.02
26/03/2022	A1	3.12
25/03/2022	B1	4.19
26/03/2022	B1	4.32

What I'd like to get is a calculated measure of the difference of value of product A in Table A minus the value of product A1 in Table B for each date, and finally draw a line chart with this calculated measure that shows the evolution of this difference. I'd like to avoid a solution involving joining these two tables through the date

Thus, for products A and A1, the values would be the following: 

On March 25th, 3.23 (which is the value of Product A) - 3.02 (which is the value of Product B)=0.21

On March 26th, 3.39 (which is the value of Product A) - 3.12 (which is the value of Product B)=0.27

Thanks in advance for your kind help 🙂

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

You can create a bridge table to connect tableA and tableB with the following formula, but you can customize the specific calculation logic for the two tables (correlation of the product fields).

Cal =
DISTINCT ( UNION ( ALL ( TableA[Date] ), ALL ( TableB[Date] ) ) )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[amitchandak]

@Anonymous , You have create a common date and product table and join with both tables. Then you can take diff in visual on a common date and product dimensions

sum(Table1[Value]) - Sum(Table2[Value])

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.