Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

comparing column row

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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    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]
            )
        )
    )
Anonymous's avatar
Anonymous
Not applicable
8 years ago
table 1    table 2   
         
invoice nr  dateamountchange rate invoice numberdateamountchange rate
100001/01/2018101 100001/01/2018101
100102/01/201851 100102/01/2018151
100102/01/201851 100203/01/2018251
100102/01/201851     
100203/01/2018201     
 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

 

Zubair_Muhammad

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Anonymous

 

So in above case what is the Final Output you desire?

Can you put final output in Table form?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
         
          
    output     
    Invoice number/amount table 1//change rate table 1//amount table 2//change rate 2//difference (amont table1-amount table 2)/
    10022012515
    1003301  30
          
          
         Total of all difference 35

     

    this would be the output

    Zubair_Muhammad

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Zubair_Muhammad

         

        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