cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Super User IV
Super User IV

@bbui , 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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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

 

Highlighted

Good morning guys,

Any solutions for this, please? 😢

Highlighted

Hi, @bbui 

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors