cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## create new column to Look up latest date from other table

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

1 ACCEPTED SOLUTION
Super User

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")

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Solution Sage

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

Frequent Visitor

Employee

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

Frequent Visitor

@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.

Super User

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")

Regards,
Ashish Mathur
http://www.ashishmathur.com
Employee

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

Frequent Visitor

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

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors