Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WBAnalyst
New Member

Data Validation using PowerBI

Hello all,

 

I am trying to validate data in two tables. Below is an example of my table structure: 

 

New Table   
DateProjectFeesCost
6/1/2022A105
6/1/2022B126
6/1/2022C137
9/1/2022A107

 

 

Old Table   
DateProjectFeesCost
6/1/2022A105
6/1/2022B126
6/1/2022C137

The result I am trying to get is:

DateFees (compare)Cost (compare)
6/1/2022SUM(OldTable(Fees))-SUM(NewTable(Fees))SUM(OldTable(Cost))-SUM(NewTable(Cost))
9/1/2022SUM(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. 

2 REPLIES 2
tamerj1
Super User
Super User

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors