Forum Discussion
Multiple Costs For Same Product over time
Hi There,
I am wanting some help with a cost table that has the same product code but over a few years with varying costs over that time so I can correctly do some profit and margin analysis for the same products over multiple FY's.
Thanks.
Example:
| Product 1 | 5.01 | 28/07/2017 |
| Product 1 | 5.05 | 1/08/2017 |
| Product 1 | 5.12 | 16/05/2018 |
| Product 1 | 5.15 | 23/07/2018 |
| Product 1 | 5.18 | 25/07/2018 |
| Product 1 | 5.21 | 3/08/2018 |
| Product 2 | 2.31 | 5/04/2017 |
| Product 2 | 2.40 | 9/07/2017 |
| Product 2 | 2.42 | 11/11/2017 |
| Product 2 | 2.48 | 13/04/2018 |
| Product 2 | 2.50 | 3/02/2019 |
| Product 3 | 0.83 | 1/02/2017 |
| Product 3 | 0.86 | 3/06/2017 |
| Product 3 | 0.87 | 1/12/2017 |
| Product 3 | 0.89 | 4/05/2018 |
| Product 3 | 0.94 | 6/10/2018 |
| Product 3 | 0.97 | 3/03/2019 |
2 Replies
- amitchandak
Super User
Assume this is the price table and you want to use the same in sales. These two columns can help
//New column in price End date of price = minx(filter(price,price[Product]=earlier(price[Product]) && price[Date]>earlier(price[Date])),price[Date]) //new Column in sales In sales table minx(filter(price,sales[Product]=price[Product] && sales[sales Date]>=price[Date] && sales[sales Date]<price[End date of price]),price[Date])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. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin 
v-lili6-msftCommunity Support
hi Luke_H
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490and if this post could meet your requirement:
Regards,
Lin
