Forum Discussion

WBAnalyst's avatar
WBAnalyst
New Member
3 years ago

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. 

Data Validation
DAX
large dataset
powerbi

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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])

    • WBAnalyst's avatar
      WBAnalyst
      New Member
      3 years ago

      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.