cancel
Showing results for
Did you mean:

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.

Anonymous
Not applicable

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

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

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.

Anonymous
Not applicable

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

Thank you so much. 🙂

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors