Forum Discussion
Helper ISearch Part Cost
Hello to everyone!
I'm trying to see the part cost rate of my Factory.
I've an table from the DB that there is all the costs of all parts of the factory.
There are item numbers that appear several times in the table but on different dates and costs.
In excel, my formula will be:
=INDEX(PART_COST_RATE[RATE],MATCH([PART_CODE]&MAX(IF([PART_CODE]=PART_COST_RATE[PART_CODE],PART_COST_RATE[EFFECT_FROM_DATE])),PART_COST_RATE[PART_CODE]&PART_COST_RATE[EFFECT_FROM_DATE],0))I search for equivalent formula in power BI.
If the field types of the two tables are synchronized, it does not matter whether text or number.
Try this:
Column = VAR a = MAXX ( FILTER ( PART_COST_RATE, [PART_CODE] = EARLIER ( 'Table'[PART_CODE] ) && [EFECT_FROM_DATE] <= EARLIER ( 'Table'[MANUFACTURE DATE] ) ), [EFECT_FROM_DATE] ) RETURN MAXX ( FILTER ( PART_COST_RATE, [EFECT_FROM_DATE] = a && [PART_CODE] = EARLIER ( 'Table'[PART_CODE] ) ), [RATE] )Are you sure there are matching results in the two tables?
Best Regards,Community Support Team _ Janey
18 Replies
- VahidDM
Super User
Hi Yonatan1984
Can you post sample data as text and expected output?
Not enough information to go on;
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
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Yonatan1984Hi VahidDM
for Exmple, this is The DB for PART_COST_RATE:
RATE EFECT_FROM_DATE PART_CODE 20 01/01/2019 111 25 01/01/2019 222 30 21/03/2020 111 45 01/01/2019 333 50 01/01/2019 444 26 30/05/2020 111 39 05/07/2021 444 10 12/09/2020 333 5 01/01/2019 555 15 03/08/2021 111 57 05/09/2021 333 16 21/03/2020 222 This is the output that I need to show:
PART_CODE MANUFACTURE DATE QNTY MANUFACTRED RATE COST 111 30/01/2019 1253 20 25,060 222 02/04/2019 1405 25 35,125 111 30/04/2020 1286 30 38,580 333 05/07/2020 836 45 37,620 444 20/12/2019 745 50 37,250 111 19/08/2019 941 20 18,820 444 10/01/2020 1086 50 54,300 333 24/04/2021 983 10 9,830 555 10/12/2020 1126 5 5,630 111 23/05/2021 1305 26 33,930 333 29/10/2019 890 45 40,050 222 23/05/2020 1332 16 21,312 - VahidDM
I think you missed to share all data tabels! Can you please explain how did you calculate QNTY or MANUFACTURE DATE?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
