Search Part Cost

[Yonatan1984]

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.

[VahidDM]

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/

[Yonatan1984]

Hi @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]

@Yonatan1984 

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/

[Yonatan1984]

Hi @VahidDM 

I did not miss nothing.

The manufacture date and the qnty base on the user input in power apps application that I build.

The rate price is the manufacture cost according to the product tree.

The rate change according to the material cost that we get from the supplier.

For exmple:

Before the covid-19, the prices was stable of the materials.

After the covid-19 started the prices are up.

The manufacture date isn't dependent of the change rate, the factory must to manufactre the items.

[VahidDM]

Hi @Yonatan1984 

Use this code to add a RATE column to the output table:

Rate Calculated Column =
VAR _A = [PART_CODE]
VAR _B = [MANUFACTURE DATE]
VAR _C =
    CALCULATE (
        MAX ( PART_COST_RATE[EFECT_FROM_DATE] ),
        FILTER (
            ALL ( PART_COST_RATE ),
            PART_COST_RATE[PART_CODE] = _A
                && PART_COST_RATE[EFECT_FROM_DATE] <= _B
        )
    )
RETURN
    CALCULATE (
        MAX ( PART_COST_RATE[RATE] ),
        FILTER (
            ALL ( PART_COST_RATE ),
            PART_COST_RATE[PART_CODE] = _A
                && PART_COST_RATE[EFECT_FROM_DATE] = _C
        )
    )

Output:

then multiply the New Rate column by QTY to find the COST.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Yonatan1984]

Hi @VahidDM 

Thanks for the code, I tested the solution and it seems that not all rates are return, their is a blank cells for some items even though they have an rate in the rates table.

other item get the right rates.

[VahidDM]

@Yonatan1984 

Can you share a sample of your data with this scenario (Blank cells with rate)?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Yonatan1984]

hi @VahidDM 

Manufactre DB:

Rates DB:

[v-janeyg-msft]

Hi, @Yonatan1984 

There should be no mistakes according to above formula.

Can you show a screenshot of your code? Let's check if you wrote it wrong.

Best Regards,
Community Support Team _ Janey

[Yonatan1984]

hi @v-janeyg-msft 

There is the code:

Rate Calculated Column = 
VAR _A = [PART_CODE]
VAR _B = [NewDate]
VAR _C =
    CALCULATE (
        MAX ( PART_COST_PRC_RATES[EFCT_FROM_DATE] ),
        FILTER (
            ALL ( PART_COST_PRC_RATES ),
            PART_COST_PRC_RATES[PART_CODE] = _A
                && PART_COST_PRC_RATES[EFCT_FROM_DATE] <= _B
        )
    )
RETURN
    CALCULATE (
        MAX ( PART_COST_PRC_RATES[RATE] ),
        FILTER (
            ALL ( PART_COST_PRC_RATES ),
            PART_COST_PRC_RATES[PART_CODE] = _A
                && PART_COST_PRC_RATES[EFCT_FROM_DATE] = _C
        )
    )

There is the screen shot:

[v-janeyg-msft]

Hi, @Yonatan1984 

Any updates? I don't get your reply and don’t know if your problem has been solved.

[Yonatan1984]

HI @v-janeyg-msft 

I checked and it dosen't work.

All the format are fine ang the same in all the tables.

[v-janeyg-msft]

Hi, @Yonatan1984  

If the data type and the table structure are the same, it is logically impossible to have problems. Did you download my file and compare it with your source file? According to the sample you provided, we can only help you here unless you can provide further details.

Best Regards,

Community Support Team _ Janey

[Yonatan1984]

Hi @v-janeyg-msft 

I checked, and the format of part_code in the tables is text and the date format is date in short date.

I checked your model, and it seems that the part_code format in your model is whole number.

There's a screenshot:

[v-janeyg-msft · Accepted solution]

@Yonatan1984 

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

[Yonatan1984]

Hi @v-janeyg-msft 

Now it's work!

Thanks!!

[v-janeyg-msft]

@Yonatan1984 

There is my code in the sample I gave you at the beginning... It's okay now.

Best Regards,

Community Support Team _ Janey

[v-janeyg-msft]

Hi, @Yonatan1984 

Try to change the datetime format of your date column to date column.

Below is my sample. You can check how it differs from your original file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey