Inventory first in first out

[Cynwaa]

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!

[v-lili6-msft · Accepted solution]

hi, @Cynwaa

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

[Cynwaa]

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.

[Cynwaa]

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.