Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
so I've 2 different table
both tables contains a column called invoice nr,( the tables contain more column such as date and so on) my goal is to create a measure (or something else that could do the trick) to filter those columns for the same date period and see what are the difference between those 2 tables ( invoice that are in the first table not contained on second
Solved! Go to Solution.
Try this revision
Please see revised file as well
CalculatedTable = VAR temp = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Invoice Nr] ), VALUES ( ExtractTESTv4[Invoice Nr] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Date] ), VALUES ( ExtractTESTv4[Date] ) ) ) RETURN GENERATE ( temp, CALCULATETABLE ( SELECTCOLUMNS ( InvoiceLineTESTv4, "Date", [Date], "Other Column1", [Other Column.1] ) ) )
so I've 2 different table
both tables contain a column called invoice nr,( the tables contain more column such as date and so on) my goal is to create a table to filter those columns for the same date period and see what are the difference between those 2 tables in the column amount -> if the amount is different for the same invoice, I need to display invoice number, amount in 1 table, amount in 2 table, and the different
what i could do it is to create a table, but i cant get data from 2 table and it is no work as should
CalculatedTable2 = VAR temp = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Total in CZ] ), VALUES ( ExtractTESTv4[Total in CZ] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Invoice.Nr] ), VALUES ( ExtractTESTv4[Document No.] ) ) )
RETURN GENERATE ( temp, CALCULATETABLE ( SELECTCOLUMNS ( InvoiceLineTESTv4, "Date", [Invoice.dtDate], "Amount XSP", [Total in CZ],"Invoice nr", [Invoice.Nr])))
in the screenshot, you can see that the table so create give me a result even if the amount it is same
but looks that display also invoice with the same amount
I think to depend on the fact that in the 1 table some invoice are split as shown in the image below (very few of them)
and in the second table, there is just one line for this invoice ( the total amount are the same in both tables )
if it is possible to like this:
basically the sum of invoice number 0000 (table1) = invoice number 0000 (table 2)
Could anyone help?
@Zubair_Muhammad maybe 🙂
HI @snifer
Could you copy paste few rows of from each Table with Expected Ouput
For some reason I am not able to access Drop Box
For example
Table1
Employee ID | Supervisro ID | Validation Result |
WS2001 | 7345382 | Valid |
WS2002 | Invalid | |
WS2003 | WS2001 | Valid |
WS2004 | WS2004 | Invalid |
Expected Output
??
https://drive.google.com/file/d/1zwKegBu_WuBwNgWGE73c2VICnKf2vq8I/view?usp=sharing
can you try this link
#
this is the image for explaining you better
https://drive.google.com/open?id=1tP2kmgCsrr1vN6YHKNvlIt67_0224Tnk
table 1 | table 2 | |||||||
invoice nr | date | amount | change rate | invoice number | date | amount | change rate | |
1000 | 01/01/2018 | 10 | 1 | 1000 | 01/01/2018 | 10 | 1 | |
1001 | 02/01/2018 | 5 | 1 | 1001 | 02/01/2018 | 15 | 1 | |
1001 | 02/01/2018 | 5 | 1 | 1002 | 03/01/2018 | 25 | 1 | |
1001 | 02/01/2018 | 5 | 1 | |||||
1002 | 03/01/2018 | 20 | 1 | |||||
1003 | 04/01/2018 | 30 | 1 |
so I need to check invoice number in both table and match that is the same amount for same invoice nr, if not should give as output:
invoice number, the amount in 1 table, amount in 2 table, and the difference between the amount, and with a column for change rate from table 1 and 1 column with change rate from table 2.
problem is that if you see on table 1 some invoice is split into multiple lines, in this case, the sum of the line with same invoice number in table 1 should be equal to same invoice nr amount in table 2.(always in one line)
+ in case that invoice number in table 1 is not present in table 2 than as output should have just the invoice nr and amount and change rate from table 1
So in above case what is the Final Output you desire?
Can you put final output in Table form?
output | |||||
Invoice number | /amount table 1/ | /change rate table 1/ | /amount table 2/ | /change rate 2/ | /difference (amont table1-amount table 2)/ |
1002 | 20 | 1 | 25 | 1 | 5 |
1003 | 30 | 1 | 30 | ||
Total of all difference 35 |
this would be the output
Please see attached file and let me know if it helps
I can't create an active connection between table 1 and table 2 for invoicenumber because I connected before those2 tables to a calendar table(created by me) in order to filtrate this 2 table for the same time frame.(
how can i do to filtrate table at the same time for the specific time frame and create a correlation for invoice number?
c
check error below
Same date period means...same day or same month or same year?
it is the same date in this format
dd/mm/yyyy
I already did, a create a correlation with a calendar table created by me, now I need to compare the invoice number column in the those different table, how I can do that?
i try to create this new column but i get an error
Transfered =
VAR Test =
COUNTROWS (
FILTER (
InvoiceLineTESTv4,
InvoiceLineTESTv4[Invoice.Nr] = ExtractTESTv4[Column4]
)
)
RETURN
IF ( Test = 1, "Yes", "No" )
A single value for column 'Column4' in table 'ExtractTESTv4' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
HI,
Please try this calculated table
From the Modelling Tab>>>New Table
CalculatedTable = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Invoice Nr] ), VALUES ( ExtractTESTv4[Invoice Nr] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Date] ), VALUES ( ExtractTESTv4[Date] ) ) )
Please see attached sample file as well
#thank you so much @Zubair_Muhammad
looks to work,
now the calculated table give me the result for all the column ( all the period)
but I need to be filtered by table calendar Testv4, how I can do that?
now I get as result the missing invoice number(I that is very good) but I would like to see the details of this invoice store in the table Invoice line testv4
in your example my question it is on the calculated table you got result invoice nr, but I would like have also other info as the date
Try this revision
Please see revised file as well
CalculatedTable = VAR temp = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Invoice Nr] ), VALUES ( ExtractTESTv4[Invoice Nr] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Date] ), VALUES ( ExtractTESTv4[Date] ) ) ) RETURN GENERATE ( temp, CALCULATETABLE ( SELECTCOLUMNS ( InvoiceLineTESTv4, "Date", [Date], "Other Column1", [Other Column.1] ) ) )
thank you for the solution you offered me,
if i wanna add compare the date the number invoice and the amount($) , how i can add the parameter amount the formula?
in the calculated table now i dispplay date just from 1 table can i add the amount of the 2 table as well? how?
thank u
i try to
CalculatedTable2 = VAR temp = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Invoice.Nr] ), VALUES ( ExtractTESTv4[Document No.] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Total in CZ] ), VALUES ( ExtractTESTv4[Total in CZ] ) ) )
RETURN GENERATE ( temp, CALCULATETABLE ( SELECTCOLUMNS ( InvoiceLineTESTv4, "Date", [Invoice.dtDate], "Total XSPED", [Total in CZ],"Invoice Reference", [Invoice.Reference] ) ) )
what i want to compare invoice number to see if same invoice have same amount
HI @snifer
Try this calculated Table
As I understand,, you want to add Amount from Table2 where Date and Invoice Number match..Right??
CalculatedTable2 = ADDCOLUMNS ( InvoiceLineTESTv4, "Table2 Amount", CALCULATE ( SUM ( ExtractTESTv4[Amount] ), FILTER ( ExtractTESTv4, [Invoice Nr] = InvoiceLineTESTv4[Invoice Nr] && [Date] = InvoiceLineTESTv4[Date] ) ) )
so this is the link to file I'm using, so it will be more clear
https://www.dropbox.com/s/5crm2812zkkoekv/Test2.pbix?dl=0
the part number 1 i used your code to check the invoice number and the date, looks to work fine!
on part 2 I need to check the invoice number and the amount ( sometime invoice on table one have a different amount of invoice in table 2)
if one amount is different I need to display:
invoice number, the amount in table 1, the amount in table 2 and the difference between them
image link
https://www.dropbox.com/s/mlpyc73bra6k33r/Capture.PNG?dl=0
1 problem is that checking the data some invoice on table one is split in more line (the same invoice in table 2 is always in 1 line)
so in case, the invoice is split in more line I need to check the some of this line with same invoice number = to invoice amounth in table 2
example of split invoice check invoice number 40001412 ( is on 3 lines) in table 1 (InvoiceLineTESTv4)