comparing invoices with multiple line in one table and single in other

[snifer]

table 1			table 2
invoice number	money		invoice number	money
1000	100		1000	50
1001	10		1000	50
1002	10		1001	10
			1002	5

this is an example of my data:

in table 1 is an invoice number and a money sum

same in table 2

problem is in table 2 sometime invoice have the same number invoice in my example the invioce1000 because the amount is divided in more line(in this case 2 lines 50+50)

I need to calculate that invoice 1000 in table 1is = to invoice 1000 in table 2 100=50+50 if not telling me the difference

this will be the output for the example

output
1000	0
1001	0
1002	5

[Ashish_Mathur]

Hi,

You may download my solution from this PBI file.

Hope this helps.

[v-yulgu-msft]

Hi @snifer,

New a calculated table:

Table3 =
SUMMARIZE (
    table1,
    table1[invoice number],
    "Difference", SUM ( table1[money] )
        - CALCULATE (
            SUM ( table2[money] ),
            FILTER ( table2, table2[invoice number] = EARLIER ( table1[invoice number] ) )
        )
)

Best regards,

Yuliana Gu

[snifer]

@v-yulgu-msft thank you it seems to work 🙂 🙂

can I ask one more question? if I want to add to this new table other columns from table 1 and table 2, can I?

I need to check that the date from invoice on table 1 is same on table 2, and if changing rate in table 1 is the same in table 2

and for last if invoice number is missing in table 1 or table 2

table 1

table 2

invoice number

money

date

changing rate

invoice number

money

date

changing rate

output

invoice nr

money table 1

money table 2

difference

date table 1

date table 2

date difference (days/months)

changing rate table 1

changing rate table 2

changing rate difference

invoice number missing in table 1 or 2

example output yes or not

output yes or no

output yes, missing in table 1(or 2) or not missing

[v-yulgu-msft]

Hi @snifer,

Table3 =
SUMMARIZE (
    table1,
    table1[invoice number],
    "Money Table1", SUM ( table1[money] ),
    "Money Table2", SUM ( table2[money] ),
    "Difference", SUM ( table1[money] ) - SUM ( table2[money] ),
    "date table1", LASTNONBLANK ( table1[date], 1 ),
    "date table2", LASTNONBLANK ( table2[date], 1 ),
    "date difference", IF (
        LASTNONBLANK ( table1[date], 1 ) = LASTNONBLANK ( table2[date], 1 ),
        "Yes",
        "No"
    ),
    "change rate table1", AVERAGE ( table1[changing rate] ),
    "change rate table2", AVERAGE ( table2[changing rate] ),
    "changing rate difference", IF (
        AVERAGE ( table1[changing rate] ) = AVERAGE ( table2[changing rate] ),
        "Yes",
        "No"
    ),
    "invoice number missing", IF (
        LOOKUPVALUE (
            table2[invoice number],
            table2[invoice number], table1[invoice number]
        )
            = BLANK (),
        "Missing in Table2",
        "Not missing"
    )
)

Best regards,

Yuliana Gu

[snifer]

https://drive.google.com/file/d/1UeHW8kIlkZBhtyL54PulcbmDnkgJc7x7/view?usp=sharing

this is the code applied to my case:

Table3 =
SUMMARIZE (
ExpenseInvoice,
ExpenseInvoice[Nr],
"Money table 1", SUM ( ExpenseInvoice[Total in CZ] ),
"Money table 2", SUM ( 'Recn tool Cost'[Total in cz] ),
"Difference", SUM ( ExpenseInvoice[Total in CZ] ) + SUM ( 'Recn tool Cost'[Total in cz] ),
"date table1", LASTNONBLANK ( ExpenseInvoice[dtAccounting].[Date], 1 ),
"date table2", LASTNONBLANK ( 'Recn tool Cost'[Posting Date].[Date], 1 ),
"date difference", IF (
LASTNONBLANK ( ExpenseInvoice[dtAccounting], 1 ) = LASTNONBLANK ( 'Recn tool Cost'[Posting Date], 1 ),
"Yes",
"No"
),
"change rate table1", AVERAGE ( ExpenseInvoice[CurrencyRate] ),
"change rate table2", AVERAGE ( 'Recn tool Cost'[Currency Factor] ),
"changing rate difference", IF (
AVERAGE ( ExpenseInvoice[CurrencyRate] ) = AVERAGE ('Recn tool Cost'[Currency Factor] ),
"Yes",
"No"
),
"invoice number missing", IF (
LOOKUPVALUE (
'Recn tool Cost'[Document No.],
'Recn tool Cost'[Document No.], ExpenseInvoice[Nr]
)
= BLANK (),
"Missing in Table2",
"Not missing"
)
)

as output, I can see that the value from the table 2 are reported wrongly in all column (always same number)

date difference does not work properly and money difference not working 😞

any ideas? @v-yulgu-msft

I need to search also invoice nr from table 2 missing in table 1

please help

I attach the file, so you can try it

https://drive.google.com/file/d/1UeHW8kIlkZBhtyL54PulcbmDnkgJc7x7/view?usp=sharing