Forum Discussion

Yonatan1984's avatar
Yonatan1984
Icon for Helper I rankHelper I
4 years ago
Solved

Search 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...
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

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's avatar
Yonatan1984
Icon for Helper I rankHelper I
4 years ago

Hi VahidDM 

for Exmple, this is The DB for PART_COST_RATE:

RATEEFECT_FROM_DATEPART_CODE
2001/01/2019111
2501/01/2019222
3021/03/2020111
4501/01/2019333
5001/01/2019444
2630/05/2020111
3905/07/2021444
1012/09/2020333
501/01/2019555
1503/08/2021111
5705/09/2021333
1621/03/2020222

 

This is the output that I need to show:

PART_CODEMANUFACTURE DATEQNTY MANUFACTREDRATECOST
11130/01/2019125320          25,060
22202/04/2019140525          35,125
11130/04/2020128630          38,580
33305/07/202083645          37,620
44420/12/201974550          37,250
11119/08/201994120          18,820
44410/01/2020108650          54,300
33324/04/202198310            9,830
55510/12/202011265            5,630
11123/05/2021130526          33,930
33329/10/201989045          40,050
22223/05/2020133216          21,312
  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Yonatan1984
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        VahidDM
        Icon for Super User rankSuper User
        4 years ago

        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/