Forum Discussion
Calculate cost based on transaction date
Hello,
I have a Transaction table with the following structure:
DATE SKU UNITS
And I have a Cost table like this:
STARTING DATE OF THE COST SKU COST
What I need is to have the next table:
TRANSACTION DATE SKU UNITS COST
My current approach is to create a calculated column in the Transaction table with this formula:
And it gives me Error and the following message:
"A table of multiple values was supplied where a single value was expected."
Both tables repeat skus since one is for transactions and the other provides costs at a given date.
Please help.
RodrigoCenteno , Try
New column =
var _max = maxx(FILTER (Cost,Transaction[SKU] = Cost[SKU] && Transaction[DATE] <= Cost[STARTING DATE OF THE COST]),Cost[STARTING DATE OF THE COST])
return
maxx(FILTER (Cost,Transaction[SKU] = Cost[SKU] && Transaction[DATE] =_max ),Cost[cost])
2 Replies
- amitchandak
Super User
RodrigoCenteno , Try
New column =
var _max = maxx(FILTER (Cost,Transaction[SKU] = Cost[SKU] && Transaction[DATE] <= Cost[STARTING DATE OF THE COST]),Cost[STARTING DATE OF THE COST])
return
maxx(FILTER (Cost,Transaction[SKU] = Cost[SKU] && Transaction[DATE] =_max ),Cost[cost])Actually it didnt work. I made a check and it only gives me the values where the transaction date equals the starting date of the cost, and miss all the values after that date. I tried to correct it by modifying "Transaction[DATE] =_max" this by < or > but didnt work.
