Multiple Costs For Same Product over time

[Luke_H]

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

[v-lili6-msft]

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/38490

and if this post could meet your requirement:

https://community.powerbi.com/t5/Desktop/Need-a-column-to-show-latest-transacted-price-for-each-prod...

Regards,

Lin

[amitchandak]

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