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.