Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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])
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
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.
If it doesn't meet you requirement,please share more details.
Best Regards,
Community Support Team _ Eason
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |