Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Compare Data - Different Tables & multiple Rows

Hi, 

 

I was trying to use formula previously suggested @ https://community.powerbi.com/t5/Desktop/How-to-compare-data-current-vs-previous-in-a-dynamic-table/...  but it didn't work as i expected. 

I will add a sample of my data; 

As you can see i have two related tables, unique identifier is "BK_NUMBER" for both. Data is more complicated but i share necessary parts

First 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 & previous comparison should be customer basis (a customer's last order and previous order rates will be shown in different columns) 

all bk_numbers are connected to a customer (third sheet is a sample) 

 

BK_NUMBERCODECURRENCYRATE
082xxxx5606ERCUSD75
082xxxx5606CAFUSD23.09
082xxxx5606THCUSD100
082xxxx5606CSFUSD11
082xxxx5606PRMUSD18.9
082xxxx5606BRCUSD128
082xxxx5606PRSUSD75
082xxxx5606ONCUSD360
082xxxx5606FRTUSD809.01
082xxxx5607ERCUSD100
082xxxx5607PRMUSD16.83
082xxxx5607CAFUSD16.58
082xxxx5607SCSUSD25
082xxxx5607FRTUSD418.59
082xxxx5607CSFUSD11
082xxxx5607BRCUSD77
082xxxx5607DMGUSD75
082xxxx5607OCCUSD75

 

BK_NOBK_CRT_DATE
082xxxx560601-Eyl-20
082xxxx560601-Eyl-20
082xxxx560601-Eyl-20
082xxxx560601-Eyl-20
082xxxx560601-Eyl-20
082xxxx560601-Eyl-20
082xxxx560701-Eyl-20
082xxxx560701-Eyl-20
082xxxx560701-Eyl-20
082xxxx560701-Eyl-20
082xxxx560701-Eyl-20

 

BK_CUSTOMER
XYZ  LTD
ABC LTD
ZXC SA
ASD LTD

 

Capture.JPG

4 REPLIES 4
Anonymous
Not applicable

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 @V-pazhen-msft 

Hi @Anonymous,

If you want to compare current value and previous value, your table requires regular 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 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi @

 

HI @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 

Notice: please not attach the sensitive data in the sample file.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.