Forum Discussion

RodrigoCenteno's avatar
RodrigoCenteno
New Member
4 years ago
Solved

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 ne...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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

RodrigoCenteno's avatar
RodrigoCenteno
New Member
4 years ago

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.