Vlookup and calculation

[KatkaS]

Hello,

I'm struggling with following:

I have two tables and I need to calculate the difference between amounts in two columns (one in each table),

based on the FAM line (4+ digit number).

Could you please advise what could be the solution?

Thank you very much!

[Icey]

Hi @KatkaS ,

Is the problem solved?

Best Regards,

Icey

[KatkaS]

Hello Icey,

I'm really sorry to take your time, your solution looks great, but it isn't working for me..

I Added Custom Column as per your advice, but I'm getting below error:

When I added the columnm there was no syntax error (but I had to remove ' ' signes and doubled &s, they were giving errors....), here how it looks like:

Do you think you could still have a look in this? Thank you!

[Icey · Accepted solution]

Hi @KatkaS ,

Please create the columns in Data View or Report View, not Power Query Editor. These are DAX expressions. 

Best Regards,
Icey

[KatkaS]

Thank you so much for your help, Icey!!!!!

It worked for me and the links you sent last time are very useful too!!!

[Icey]

Hi @KatkaS ,

Try this:

1. Change the data type of '2nd source SAP'[FAM Line] to 'Text'.

2. Create columns in '2nd source SAP'.

Amount of 1st = 
    CALCULATE (
        SUM ( '1st source ECS'[AMOUNT] ),
        FILTER (
            '1st source ECS',
            [BALANCE SHEET + INCOME STATEMENT - Copy.1.2]
                = EARLIER ( '2nd source SAP'[FAM Line] )
                && [PERIOD] = EARLIER ( [PERIOD] )
                && [FAM] = EARLIER ( '2nd source SAP'[Operational Company] )
                
        )
    )

Difference = '2nd source SAP'[YTD]-'2nd source SAP'[Amount of 1st]

Best Regards,
Icey

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

[Icey]

Hi @KatkaS ,

If you don't mind, please give me some data sample.

Best Regards
Icey

[KatkaS]

 So in my example above I search for the amounts to compare linked to FAM line (column C) and number in column B (Balance sheet). It must be based on the Operational company (CZC in this case, but there are many more). FAM lines are the same for all the companies.

I hope I'm clearer now..

[KatkaS]

[Icey]

Hi @KatkaS ,

It would be great if you could provide your sample data in Excel.

Best Regards,

Icey

[KatkaS]

Hello Icey,

is it ok if I email it to you?

Thank you.

[Icey]

Hi @KatkaS ,

I suggest you to use OneDrive for Business. Attach the link.

Best Regards,

Icey

[KatkaS]

Hello Icey,

here is the link, I hope you will be able to access it, thank you!!

pbix file 

[KatkaS]

Hello Icey!

Thank you for your time!

I have two tables and I have to find out that when I deduct the amounts from each other, if I get a zero difference or there is a difference (and how much). The common thing it the Fam line (it is an accounting line) and Company abbreviation. I also need to be able to filter based on Period (I usually have Slicer for this).

The outcome should be Matrix: Company / FAM line / Difference...

[amitchandak]

You can bring the field from one table to another like this if needed

Max customer = MAXX(filter(Sales,Sales[Order Id]='Item'[Max Sales order id] && Sales[item_id]='Item'[Item ID]),Sales[Customer ID])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[KatkaS]

Thank you, Amitchandak!

I was not clear enough, I think, could you please check additional information I just posted with screen print?

Thank you very much for your time!

[mwegener]

Hi.

• Load both tables into Power Query.
• Prepare your key column.
• Merge the Queries
• Calculate the difference

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[KatkaS]

Thank you for your support!

But I don't understand how merging could help, perhaps I'm missing something? One column is the common information for both tables and I would need to calculate if there is a difference in the amounts based on that information and operational company.

I tried to explain myself better in detail below.

Thank you for checking this!