How can I find a value in a earlier date?

[RaphaelQueiroz]

Hi guys, let's say 05/01/2017 I sold a product for $ 25.00 and I need to know the cost of it on this day, however, in the historical cost table there is no information of this product on this day, just in 04/15/2017, 04/21/2017 and 05/07/2017. I need to get the cost of the date 04/21/2017, the earliest date of my sale. How can I get this value? Thanks

[TomMartens]

Hey,

w/o some sample data this is a little difficult, but I would try this:

creating a calculated column in the sales table like so

LastPrice =

var currentDate = 'salestable'[SalesDate]
var currentProduct = 'salestable'[Product]

lastCostDate = 
CALCULATE(
MAX('costtable'[nameofthedatecolumn])
,'costtable'[Product] = currentProduct &&
'costtable'[nameofthedatecolumn] <= currentDate
)
return
LOOKUPVALUE(
'costtable'[cost]
,'costtable'[Product], currentProduct
,'costtable'[nameofthedatecolumn] . lastCostDate
)

Hopefully this gets you started

Regards

Tom

[RaphaelQueiroz]

Thanks for reply, here is my queries:

Table where my sale date is (format dd/mm/yyyy):

Table where my sales is

Table where my product ID is

Maybe this help

[TomMartens]

Hey,

please upload your file to onedrive or dropbox.

Did you already tried my calcualtion, what has been the result?

Regards

Tom

[RaphaelQueiroz]

"please upload your file to onedrive or dropbox."

My data is in a SQL database, if I upload the file, you will not be able to access it.

"Did you already tried my calcualtion, what has been the result?"

Actually, I did not quite understand. It's all a single measure?

[v-yulgu-msft]

Hi @RaphaelQueiroz,

Not very clear about your table structure. Based on my test, TomMartens' suggestion could return correct cost value. To make the formula apply to your scenario, you should make sure costtable and salestable contain below fields. You can use LOOKUPVALUE function to add columns, or use RELATED function to reference to columns existing in other table if there existing relationships.

       

Then, please create a calculated column in 'salestable' using below code, I made some adjustment based on TomMartens' DAX formula:

LastPrice = 

var currentDate = 'salestable'[SalesDate]
var currentProduct = 'salestable'[Product]

var lastCostDate = 
CALCULATE(
MAX('costtable'[costdate])
,FILTER(costtable,'costtable'[Product] = currentProduct &&
'costtable'[costdate] <= currentDate
))
return
LOOKUPVALUE(
'costtable'[cost]
,'costtable'[Product], currentProduct
,'costtable'[costdate], lastCostDate
)

Result.

Best regards,
Yuliana Gu

[RaphaelQueiroz]

Thanks for answer!

it is showing an error: the expression refers to several columns. It's not possible convert multiple columns to a scalar value. I think the error is at the beginning of max.

[v-yulgu-msft]

Hi @RaphaelQueiroz,

This error might be related to certain data. Please show me your dataset in Power BI dekstop.

Regards,
Yuliana Gu

[RaphaelQueiroz]

@v-yulgu-msft wrote:

Hi @RaphaelQueiroz,

This error might be related to certain data. Please show me your dataset in Power BI dekstop.

Regards,
Yuliana Gu

---

What do you need exactly?

[v-yulgu-msft]

Hi @RaphaelQueiroz,

Have you achieved your requirement? If so, would you please kindly mark the helpful reply as an answer or share your solution so that it can benefit more users?

Thanks,
Yuliana Gu

[RaphaelQueiroz]

---

@v-yulgu-msft wrote:

Hi @RaphaelQueiroz,

Have you achieved your requirement? If so, would you please kindly mark the helpful reply as an answer or share your solution so that it can benefit more users?

Thanks,
Yuliana Gu

---

Nope, I dont know how I can send to you my data set.

[v-yulgu-msft]

Hi @RaphaelQueiroz,

I need the data table loaded to your desktop. Did you create a measure or calculated column using above DAX formula?

Regards,
Yuliana Gu