create new column to Look up latest date from other table

[jaafer]

I have two tables  like  table 1  "item master" and table 2 "item ledger" i want to create a new column in item master table as "last sale date" one solution is to create a new table with summarize function. But as i have direct query with sql data source i cannot create a new table . so i need to create a column which looks up the latest date from ledger entry table. Somebody can help me. i   

[jthomson]

This looks awfully similar to the question you asked yesterday, what's different?

[jaafer]

[v-huizhn-msft]

Hi @jaafer,

If there is relationship between the two tables? last sale date is for whole table or each item? Please share more details for further analysis.

Best Regards,
Angelia

[jaafer]

@v-huizhn-msft

Hi @angelia , There is relationship item master table[item no] to item ledger entry table [item no]. And i want to see the last sale date of each item . To be clear . I want add a new column in item master table as ‘las sale date’ Thank you for your reply.

[Ashish_Mathur · Accepted solution]

Hi @jaafer,

As long as there is a relatiosship from the Item No column of the Sales Data Table to the Item No column of the Items table, this formula will work

=CALCULATE(MAXX(RELATEDTABLE(Sales),[Posting date]),Sales[Entry type]="Sale")

[v-huizhn-msft]

Hi @jaafer,

Please create a date calculated column, then get the last sale date based on the [date] column using the formulas below and check if it works

date=RELATED(ledgerentrytabel[date])
Last_sale=CALCULATE(MAX(mastertable[date]), ALLEXCEPT(mastertable,mastertable[item]))

Best Regards,
Angelia

[jaafer]

Hi  I tried . But showing error I have attached screenshot My relationship also mentioned in the screenshot

[jaafer]

@Angelia, Thank you very much for ur solution. I will check it tomorrow. Today i m off .😊 i will let u know the result

[jaafer]

Thanks for reply . I cannot create a new table. My Data source is direct query from sql.