Get last sales price on my items in a new column

[Anonymous]

Hi everyone

I have 2 tables.

One is a simple table of my items and descriptions of them.

The other is a table of sales made. It looks like this

Item	Description	Quantity	Sales Amount	Date
001	Bike	10	2000	26-04-2021
001	Bike	10	2000	27-04-2021
001	Bike	20	3000	27-04-2021
002	E-Bike	10	5000	26-04-2021
002	E-Bike	10	4500	27-04-2021

I need to create a new column on my items list that shows the last sales price per unit for each item.

If several sales has happened on the same day for a single item i need the one with the largest price per unit. For example item 001. There are 2 sales on the 27th, and even though one sold for 3000, the other one has a better price per unit 2000/10 = 200.

In the end id like my Items table to look like this:

Item No.	Description	Last Sales Price Per Unit
001	Bike	200 (2000/10)
002	E-Bike	450 (4500/10)
etc.	etc.	etc.

Any and all help is appreciated

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below for creating a column.

Sales Price Per Unit =
VAR currentdescription = Items[Description]
VAR maxdate =
MAXX ( FILTER ( Sales, Items[Description] = currentdescription ), Sales[Date] )
VAR groupbyunitprice =
GROUPBY (
FILTER ( Sales, Sales[Description] = currentdescription ),
Sales[Date],
"@unitprice", MAXX ( CURRENTGROUP (), Sales[Sales Amount] / Sales[Quantity] )
)
VAR findmaxdateunitprice =
FILTER ( groupbyunitprice, Sales[Date] = maxdate )
RETURN
SUMX ( findmaxdateunitprice, [@unitprice] )

https://www.dropbox.com/s/wlr878f1lijwsth/hede.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

I am having a hard time even understanding the solution, but it works like a charm.

Thank you so much. 🙂