Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jaafer
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. item ledger.jpgi   item table.jpg

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
jthomson
Solution Sage
Solution Sage

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

DEAD STOCK REPORTS - Power BI Desktop.jpg

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

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

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

date column.jpg

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

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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