Last sale price

[dojobrady]

I have a dataset with unit level e-commerce sales. In a table I'm trying to display the last sale price for each unique title separated by marketplace.

I first created a measure called "last sold date", which takes the lastnonblank of order closed date and displays it. It works fine. As a note, order closed date has a date and a timestamp. So it is super specific and should have no duplicates.

I then tried to create a measure for last sale price, which was basically calculate(average(sale price),last sold date = order closed date.

It won't take that measure because it can't have true/false equations in calculate formula.

I then created a column and it says if(order closed date=last sold date,sale price, blank())

However it is not displaying the correct value and there are no blanks. It's very confusing.

Basically the data is structured as such (simplified). UnitID is unique to the unit, but title could be duplicated if more than one of the same type of product sold. I pulled sales for past three months.

Title UnitID. Sale price. Marketplace.
Order closed date

[amitchandak]

Please refer if this example can help 

https://community.powerbi.com/t5/Desktop/Need-a-column-to-show-latest-transacted-price-for-each-prod...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

[Anonymous · Accepted solution]

Hi dojobrady,

You can try a direct filter on the Max closed date, e.g.:
CALCULATE(
 AVERAGE( tablename[Sale Price] )
,FILTER( tablename, tablename[order closed date] = MAX( tablename[order closed date] )
)

This will then filter your table on the last order closed date in your current filter context, allowing you to select any date or range of dates from this table and the get the average sale price for the last date in that selection, per whatever other field(s) you use to slice this data.

[dojobrady]

Fixed! So, for me to learn:

Calculate can't do true false equations, but if you do calculate, you can add that filter formula to it to make it do the same thing?