Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |