LOOKUPVALUE date related

[Anonymous]

Dear experts here, 

I really need your help to find the solution for this. Currently stuck here T_T 

I have 2 tables, DOOR & Sell out. 
I want to add 1 lookupvalue col which idea result as highlighted. And also need 1 measure to calculate total of sale. 

Thank so much :x 

[amitchandak]

@Anonymous , Not very clear. Can you share sample data and sample output in table format?

Copy data in the related table on the M side table from 1 side

// new column in sales Table
Item Name = RELATED('item'[Brand])

Copy City wthout using related // new column in sales Table 
City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City]) // error for datatype

Copy City wthout using related // new column in geography from sales
City Name = Sumx(FILTER(Sales,geography[City Id]=Sales[City Id]),Sales[Amount]) // error for datatype

//New column in sales from Month Year

Month Name = LOOKUPVALUE('date'[Month Year],'date'[Date],Sales[Sales Date])

[Anonymous]

Hi @amitchandak 

thank so much for responsding 🙂 

My prob here is, I have customer, who "DOOR" unchanged, however, the customer name & cust code are change after effected date. 

The Customer master as below: 

Country	Door	Customer name	Customer Code	Sale Rep	New Customer name	New customer code	Effect date
Cambodia	DFS Siem Reap DT	DFS VENTURE S'PORE (PTE) LTD	256231	TR SEA	DFS SEA AUST	265121	1-Jul-19
China	CDFG Boao DT	CDFG BOAO DUTY FREE STORE	262910	TR China & Taiwan
Australia	JR Brisbane AP	JAMES RICHARDSON CORP. PTY LTD	217864	TR Oceania	LOTTE TRAVEL RETAIL AU PTY LTD	262623	1-Jan-20
Korea, Republic of	GDF Daegu DT - Grand Hotel	BLUEBELL KOREA LTD	232904	TR Korea	GRAND DUTY FREE SHOP	261704	1-Sep-20
Korea, Republic of	HDC SHILLA Seoul DT- I'Park	BLUEBELL KOREA LTD	232904	TR Korea	HDC SHILLA DUTY FREE LTD	256326	1-Sep-20
Korea, Republic of	HYUNDAI Seoul DT Gangnam	BLUEBELL KOREA LTD	232904	TR Korea	HYUNDAI DEPARTMENT STORE DF SHOP	261671	1-Sep-20
Korea, Republic of	DOOSAN GRP Seoul DT	BLUEBELL KOREA LTD	232904	TR Korea
Korea, Republic of	ENTAS Incheon DT	BLUEBELL KOREA LTD	232904	TR Korea

I also have a monthly sale report from each "DOOR". 

Date	Door	Item code	Sell out qty
1-Jun-19	DFS Siem Reap DT	ABC	xxx
3-Jul-19	DFS Siem Reap DT	ABC	xx

If we look at "DFS Siem Reap DT", after 30 Jun 2019, it become "DFS SEA AUST" 

the result i would like to archive is 

Date	Door	Item code	Sell out qty	Result
1-Jun-19	DFS Siem Reap DT	ABC	xxx	DFS VENTURE S'PORE (PTE) LTD
3-Jul-19	DFS Siem Reap DT	ABC	xx	DFS SEA AUST

For Sumarize, the total sale should be combine both before and after effect date. 

Table Sumarize
Door	Total sale
DFS Siem Reap DT	xxx + xx

Hope it clear. 

Looking forward for the solution :x

[Anonymous]

Good morning guys,

Any solutions for this, please? 😢

[v-easonf-msft]

Hi, @Anonymous 

Try to create a calculated column as below:

Result customer = 
var Current_date=DOOR[Date]
var Effect_date=RELATED('Sell out'[Effect date])
var Customer=RELATED('Sell out'[Customer name])
var New_customer=RELATED('Sell out'[New Customer name])
return IF(Current_date >Effect_date,New_customer,Customer)

For more details,please check attached pbix file.

If it doesn't meet you requirement,please share more details.

Best Regards,
Community Support Team _ Eason