Get values from related company in same table

[BHarm65]

Hi All,

I am strugeling with the following data questing.

We have intercompany transactions between differeent companies.

All the transactions are in the tabel GL_IC_Entries.

A transaction is marked with the sending company and the receiving company with a value and a posting / transaction date.

We want to have an overview if all entries between the different companies are balanced per week / month

I tried diffente approaches with calculated columns and measures with lookupvalues, but I don't get the correct data in my table.

Overview GL_IC_Entries example

Company	Posting Date	Account	Description	Amount	IC Company
CompanyA	1-2-2023	1401	IC Transfer	1.000,00	CompanyB
CompanyA	1-2-2023	1401	IC Transfer	2.000,00	CompanyC
CompanyB	3-2-2023	1401	IC Transfer	5.000,00	CompanyD
CompanyB	1-2-2023	1401	IC Transfer	1.000,00	CompanyA
CompanyD	3-2-2023	1401	IC Transfer	5.000,00	CompanyB

Wanted result

Date selector at the top where I can select the year / month or week

Date = Month February 2023

Period	Company	Amount	IC Company	Amount	Difference
2023, February	CompanyA	1.000,00	CompanyB	1.000,00	0
2023, February	CompanyA	2.000,00	CompanyC	0,00	2.000,00
2023, February	CompanyB	1.000,00	CompanyA	1.000,00	0
2023, February	CompanyB	5.000,00	CompanyD	5.000,00	0
2023, February	CompanyD	5.000,00	CompanyB	5.000,00	0

Based on this outcome we can conclude that one of the IC entries is not balanced between CompanyA and CompanyC in the month of February.

Can someone advice me how to create a solution.

Many thanks in Advance

[v-tangjie-msft · Accepted solution]

Hi @BHarm65 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create two measures. 

IC_Amount = 
var _a=CALCULATE(MIN('Table'[Amount]),FILTER(ALL('Table'),'Table'[Company]=MAX('Table'[IC Company]) &&'Table'[Posting Date]=MAX('Table'[Posting Date] )))
return
IF(_a=BLANK(),0,_a)

Difference = 
var _a=MAX('Table'[Amount])-'Table'[IC_Amount]
return 
IF(_a=0,BLANK(),_a)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[olgad]

Hi, how are they linked with each other the tables?

Create a composite primary key in both tables

Primary Key=Table[Date]&"-"&Table[Company]&"-"&Table[IC Company]
*in one table date will be based on Period, in the other on posting date
link them on that. 
Please note that Posting Date in one table and period in the other table have to be the same format. I dont know what are the datat types of those columns, but you can create a column Date=Startofmonth(Posting Date) and  please make the same format for the period. 

[BHarm65]

Hi,

Thank for you reply.

I possibly didn't make myself clear.

All entries are in one tabe.

I created a date table and connected it to the GL_IC_Table

Visual

How do I get the amount for the IC company Calculated based on the date slicer.

The amount is in the same GL_IC_Table.

Hope this helps

Best Brian