Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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")
This looks awfully similar to the question you asked yesterday, what's different?
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
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")
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
Thanks for reply . I cannot create a new table. My Data source is direct query from sql.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.