topic Data Validation using PowerBI in DAX Commands and Tips

Data Validation using PowerBI

WBAnalyst — Fri, 17 Mar 2023 18:37:25 GMT

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.

Re: Data Validation using PowerBI

tamerj1 — Fri, 17 Mar 2023 18:55:31 GMT

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

Re: Data Validation using PowerBI

WBAnalyst — Mon, 20 Mar 2023 19:13:30 GMT

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.