Forum Discussion

QPV's avatar
QPV
Frequent Visitor
4 years ago
Solved

Fetch Cost Price based on Sales Date

Hi everyone,   I receive cost price lists from a factory every six months (or more, occasionally) in an Excel file. I also have an output from our accounting software with the sales per order. I im...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    QPV , Assume Cost has cost price with date, item  and sales has sales date ans item

     

    then a new column in sales table

     

    =

    var _max = maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]<= sales[Sales Date]), COst[Cost Date])

    return
    maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]=_max), COst[Cost Date])

     

    Change as per need

     

     

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

QPV , Assume Cost has cost price with date, item  and sales has sales date ans item

 

then a new column in sales table

 

=

var _max = maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]<= sales[Sales Date]), COst[Cost Date])

return
maxx(filter(Cost, cost[Item] = sales[Item] && cost[Cost Date]=_max), COst[Cost Date])

 

Change as per need

 

 

  • QPV's avatar
    QPV
    Frequent Visitor
    4 years ago

    Thank you very much, this worked!

  • QPV's avatar
    QPV
    Frequent Visitor
    3 years ago

    Dear amitchandak,

     

    Is something like this possible for reports that use DirectQuery as well? I am trying to recreate the report with DirectQuery so I don't have to manually refresh and re-upload the report for my viewers.

     

    Thanks!