Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 🙂
Solved! Go to 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.
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.
@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.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |