Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Calculate Profitability with sales and costs table with different dates

Hello! I'm a few days ago with this matter, and I do not know how to solve it!

I have a sales table with the amounts sold per month, and another of purchases with the purchases made each month. To know the profitability of the month, yes I have the sales and quantities sold belonging to each month, but not for all the products I have purchases that same month, so that it does not cost me $ 0 for that period, I need to take the cost of the last purchase, not of the whole table but before the month I am consulting.

If anyone can help me, thank you very much! 🙏

Shopping table

Natalia0710_0-1680095792357.png

Sales Table

Natalia0710_1-1680095943681.png

2 REPLIES 2
amitchandak
Super User
Super User

@Syndicate_Admin , Please create a new column in the sales using purchase, please change column name as as per need

 

New column in sales  =

var _max = maxx(filter(purchase, Purchase[Item] = sales[item]  && Purchase[date] <=sales[date]) ,Purchase[date] )

return

maxx(filter(purchase, Purchase[Item] = sales[item]  && Purchase[date] = _max ) ,Purchase[Price] )

It worked perfect! Thanks a lot!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.