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

LOOKUPVALUE date related

Dear experts here, 

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

bbui_0-1601353232746.png

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 

4 REPLIES 4
amitchandak
Super User
Super User

@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
Not applicable

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: 

CountryDoorCustomer nameCustomer CodeSale RepNew Customer nameNew customer codeEffect date
CambodiaDFS Siem Reap DTDFS VENTURE S'PORE (PTE) LTD256231TR SEADFS SEA AUST2651211-Jul-19
ChinaCDFG Boao DTCDFG BOAO DUTY FREE STORE262910TR China & Taiwan   
AustraliaJR Brisbane APJAMES RICHARDSON CORP. PTY LTD217864TR OceaniaLOTTE TRAVEL RETAIL AU PTY LTD2626231-Jan-20
Korea, Republic ofGDF Daegu DT - Grand HotelBLUEBELL KOREA LTD232904TR KoreaGRAND DUTY FREE SHOP2617041-Sep-20
Korea, Republic ofHDC SHILLA Seoul DT- I'ParkBLUEBELL KOREA LTD232904TR KoreaHDC SHILLA DUTY FREE LTD2563261-Sep-20
Korea, Republic ofHYUNDAI Seoul DT GangnamBLUEBELL KOREA LTD232904TR KoreaHYUNDAI DEPARTMENT STORE DF SHOP2616711-Sep-20
Korea, Republic ofDOOSAN GRP Seoul DTBLUEBELL KOREA LTD232904TR Korea   
Korea, Republic ofENTAS Incheon DTBLUEBELL KOREA LTD232904TR Korea   
 

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

DateDoorItem codeSell out qty
1-Jun-19DFS Siem Reap DTABCxxx
3-Jul-19DFS Siem Reap DTABCxx

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 

DateDoorItem codeSell out qtyResult
1-Jun-19DFS Siem Reap DTABCxxxDFS VENTURE S'PORE (PTE) LTD
3-Jul-19DFS Siem Reap DTABCxxDFS SEA AUST

 

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

Table Sumarize 
DoorTotal sale
DFS Siem Reap DTxxx + xx

Hope it clear. 

Looking forward for the solution :x

 

Anonymous
Not applicable

Good morning guys,

Any solutions for this, please? 😢

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.

90.png

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

 

Best Regards,
Community Support Team _ Eason

 

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.