Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I am trying to validate data in two tables. Below is an example of my table structure:
New Table | |||
Date | Project | Fees | Cost |
6/1/2022 | A | 10 | 5 |
6/1/2022 | B | 12 | 6 |
6/1/2022 | C | 13 | 7 |
9/1/2022 | A | 10 | 7 |
Old Table | |||
Date | Project | Fees | Cost |
6/1/2022 | A | 10 | 5 |
6/1/2022 | B | 12 | 6 |
6/1/2022 | C | 13 | 7 |
The result I am trying to get is:
Date | Fees (compare) | Cost (compare) |
6/1/2022 | SUM(OldTable(Fees))-SUM(NewTable(Fees)) | SUM(OldTable(Cost))-SUM(NewTable(Cost)) |
9/1/2022 | SUM(OldTable(Fees))-SUM(NewTable(Fees)) | SUM(OldTable(Cost))-SUM(NewTable(Cost)) |
Can anyone help me with this, I tried to merge and substract the values in these tables and keep getting error.
The reason behind this is basically my dataset keeps getting updated every quarter and one of the validations we do is to make sure there is no differences in total values for the previous periods.
Hi @WBAnalyst
create a 2way many to many relationship between both date columns. Place any of the date columns in a table visual along with the following measures
Fees Compare = SUM(OldTable[Fees])-SUM(NewTable[Fees])
Cost Compare = SUM(OldTable[Cost])-SUM(NewTable[Cost])
Thanks a lot @tamerj1 !! This solution worked like a charm. I tried to create relationship on some other field, once changed to the dates it worked.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |