Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Inventory first in first out

 

the task: I have a set of data with the information of the move in dates and move out dates of production batches, and I need to check which batches violate the "first in first out" rule. And it should be on plant and product level.

 

In excel, I would make a pivot table and order the move in dates from oldest to newest, and then I can just compare the move out dates.

 

 

Is it possible to do something similar in Power BI? I have googled a lot but still have no idea how I should start.

 

Thanks a lot in advance!

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    After my research, There are two ways for you to refer to:

    a)

    Step1:

    In Edit Queries, Duplicate the basic table.

    Then add the index column for two table

    Basic table index column is starting at 1 and increment is 1

    Duplicate table index column is starting at 0 and increment is 1

     

    Step2:

    Merge two tables as below:

    Step3:

    Expand the table and Close&Apply

    Step4:

    Add a calculate column by this formula

    Result = IF(AND(Table1[Plant]=Table1[Table1 (2).Plant],Table1[Product]=Table1[Table1 (2).Product]),IF(Table1[move out date]>Table1[Table1 (2).move out date],"Violation",BLANK()),BLANK())

    Result:

     

     

    b)

    Step1:

    In Edit Queries, add an index column for the basic table 

    Step2:

    Close&Apply 

    Step3:

    Add a calculate column by this formula

    Result = 
IF (
    AND (
        Table6[Plant]
            = CALCULATE (
                MAX ( Table6[Plant] ),
                FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 )
            ),
        Table6[Product]
            = CALCULATE (
                MAX ( Table6[Product] ),
                FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 )
            )
    ),
    IF (
        Table6[move out date]
            > CALCULATE (
                MAX ( Table6[move out date] ),
                FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 )
            ),
        "Violation",
        BLANK ()
    ),
    BLANK ()
)

    Result:

     

    here is pbix, please try it.

    https://www.dropbox.com/s/mtpjwie1vanrt9i/Inventory%20first%20in%20first%20out.pbix?dl=0

     

    Best Regards,
    Lin

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous

    After my research, There are two ways for you to refer to:

    a)

    Step1:

    In Edit Queries, Duplicate the basic table.

    Then add the index column for two table

    Basic table index column is starting at 1 and increment is 1

    Duplicate table index column is starting at 0 and increment is 1

     

    Step2:

    Merge two tables as below:

    Step3:

    Expand the table and Close&Apply

    Step4:

    Add a calculate column by this formula

    Result = IF(AND(Table1[Plant]=Table1[Table1 (2).Plant],Table1[Product]=Table1[Table1 (2).Product]),IF(Table1[move out date]>Table1[Table1 (2).move out date],"Violation",BLANK()),BLANK())

    Result:

     

     

    b)

    Step1:

    In Edit Queries, add an index column for the basic table 

    Step2:

    Close&Apply 

    Step3:

    Add a calculate column by this formula

    Result = 
IF (
    AND (
        Table6[Plant]
            = CALCULATE (
                MAX ( Table6[Plant] ),
                FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 )
            ),
        Table6[Product]
            = CALCULATE (
                MAX ( Table6[Product] ),
                FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 )
            )
    ),
    IF (
        Table6[move out date]
            > CALCULATE (
                MAX ( Table6[move out date] ),
                FILTER ( Table6, Table6[Index] = EARLIER ( Table6[Index] ) + 1 )
            ),
        "Violation",
        BLANK ()
    ),
    BLANK ()
)

    Result:

     

    here is pbix, please try it.

    https://www.dropbox.com/s/mtpjwie1vanrt9i/Inventory%20first%20in%20first%20out.pbix?dl=0

     

    Best Regards,
    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-lili6-msft,

      Sorry for the late reply, thank you for the brilliant way to tackle this problem!

      It does help me a lot, on the task itself and as an idea for similar problems.