I have scenario…
Orders are raised in the 1st ERP system that is EA, then pulled to second ERP system X3 after that sales invoices are raised in x3.
We want to match the discounts and charges which are checked at EA sales order time with invoice discounts, charges etc. At EA Sales Order we have boolean flag for charges, remakes and showroom.
We have to reversal check. I know the correct discount and charge from invoice. But when I am calculating the discount then its not calculating correctly because of Joins.
Relation between EA salesorder and X3 orders is with OrderID, and x3 order and x3 sales with Document number and last invoice number. But not a active relationship. I have used USERELATIONSHIP then also result is not correct.
I tried making a summary table but when I join them I get error of circular dependency.
Can you please suggest , how can I get the correct result of measures Discounts, Remake and showroom. I have attached the test PBIX with data .
Correct amount of discount for 2021 is -6163, for customer 148099
Test_Sales.pbix (2.0 MB)