Compare Data - Different Tables & multiple Rows

Question

Hi,&nbsp;&nbsp;I was trying to use formula previously suggested&nbsp;@&nbsp;https://community.powerbi.com/t5/Desktop/How-to-compare-data-current-vs-previous-in-a-dynamic-table/m-p/1413821/highlight/true#M597890&nbsp; but it didn't work as i expected.&nbsp;I will add a sample of my data;&nbsp;As you can see i have two related tables, unique identifier is "BK_NUMBER" for both. Data is more complicated but i share necessary partsFirst i need to calculate sum of "rate" column for each "bk_number",then two different column to show last "bk_number" total rate and previous "bk_number" total rate.last &amp; previous comparison should be customer basis (a customer's last order and previous order rates will be shown in different columns)&nbsp;all bk_numbers are connected to a customer (third sheet is a sample)&nbsp;&nbsp;BK_NUMBERCODECURRENCYRATE082xxxx5606ERCUSD75082xxxx5606CAFUSD23.09082xxxx5606THCUSD100082xxxx5606CSFUSD11082xxxx5606PRMUSD18.9082xxxx5606BRCUSD128082xxxx5606PRSUSD75082xxxx5606ONCUSD360082xxxx5606FRTUSD809.01082xxxx5607ERCUSD100082xxxx5607PRMUSD16.83082xxxx5607CAFUSD16.58082xxxx5607SCSUSD25082xxxx5607FRTUSD418.59082xxxx5607CSFUSD11082xxxx5607BRCUSD77082xxxx5607DMGUSD75082xxxx5607OCCUSD75&nbsp;BK_NOBK_CRT_DATE082xxxx560601-Eyl-20082xxxx560601-Eyl-20082xxxx560601-Eyl-20082xxxx560601-Eyl-20082xxxx560601-Eyl-20082xxxx560601-Eyl-20082xxxx560701-Eyl-20082xxxx560701-Eyl-20082xxxx560701-Eyl-20082xxxx560701-Eyl-20082xxxx560701-Eyl-20&nbsp;BK_CUSTOMERXYZ&nbsp; LTDABC LTDZXC SAASD LTD&nbsp;

anonymous · Answer

i try this formula but it doesn't work. and i can't even figure out how to find previous order data

Freight Total = SUM(BK_FRT[RATE])

Last Order = Sumx(filter(allselected(BK_HEADER), [BK_CUSTOMER] = max([BK_CUSTOMER]) && [BK_CRT_DATE] = max([BK_CRT_DATE])),[Freight Total])

really need your help Greg_Deckler amitchandak Anonymous

anonymous · Answer

Hi&nbsp;Anonymous,
If you want to compare current value and previous value, your table requires regular&nbsp;numeric/date value fields then you can use this field as index to looping records and get the corresponding previous values.
If not, you can try to enter to query editor and group with current table category fields and add index inside the groups, then you can get a valid index field for dax format calculations.
Numbering Grouped Data in Power Query&nbsp;
Regards,
Xiaoxin Sheng

anonymous · Answer

Hi @v-shex-msft,&nbsp;&nbsp;Indexing is done as you suggested. Unique ID for each order. Still i can't create a formula to bring last order &amp; previous order for each customer 😕

anonymous · Answer

HI&nbsp;Anonymous,
Can you please share a pbix file with some dummy data to test? I test with your sample data but they can't link bk_numbers with customers correctly. BTW, you snapshot also not help to clarify the table relationships.
How to Get Your Question Answered Quickly&nbsp;
Notice: please not attach the sensitive data in the sample file.
Regards,
Xiaoxin Sheng

Forum Discussion

Compare Data - Different Tables & multiple Rows

4 Replies

Related content

Compare different tables

Comparing values between different tables

Power Query: compare two tables, return all differences in each of multiple columns

Compare Multiple XML Files

Compare two columns in different tables

BK_NUMBER	CODE	CURRENCY	RATE
082xxxx5606	ERC	USD	75
082xxxx5606	CAF	USD	23.09
082xxxx5606	THC	USD	100
082xxxx5606	CSF	USD	11
082xxxx5606	PRM	USD	18.9
082xxxx5606	BRC	USD	128
082xxxx5606	PRS	USD	75
082xxxx5606	ONC	USD	360
082xxxx5606	FRT	USD	809.01
082xxxx5607	ERC	USD	100
082xxxx5607	PRM	USD	16.83
082xxxx5607	CAF	USD	16.58
082xxxx5607	SCS	USD	25
082xxxx5607	FRT	USD	418.59
082xxxx5607	CSF	USD	11
082xxxx5607	BRC	USD	77
082xxxx5607	DMG	USD	75
082xxxx5607	OCC	USD	75

BK_NO	BK_CRT_DATE
082xxxx5606	01-Eyl-20
082xxxx5606	01-Eyl-20
082xxxx5606	01-Eyl-20
082xxxx5606	01-Eyl-20
082xxxx5606	01-Eyl-20
082xxxx5606	01-Eyl-20
082xxxx5607	01-Eyl-20
082xxxx5607	01-Eyl-20
082xxxx5607	01-Eyl-20
082xxxx5607	01-Eyl-20
082xxxx5607	01-Eyl-20