cancel
Showing results for
Did you mean:
Helper II

Get last sales price on my items in a new column

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

1 ACCEPTED SOLUTION
Super User

Hi, @Hede92

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

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.

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

2 REPLIES 2
Super User

Hi, @Hede92

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

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.

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

Helper II

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

Thank you so much. 🙂