cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Cynwaa Frequent Visitor
Frequent Visitor

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.

 

Unbenannt.PNG

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Inventory first in first out

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

 

2.JPG

Step2:

Merge two tables as below:

3.JPG

Step3:

Expand the table and Close&Apply

4.JPG

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:

5.JPG

 

 

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:

6.JPG

 

here is pbix, please try it.

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

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Inventory first in first out

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

 

2.JPG

Step2:

Merge two tables as below:

3.JPG

Step3:

Expand the table and Close&Apply

4.JPG

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:

5.JPG

 

 

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:

6.JPG

 

here is pbix, please try it.

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

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Cynwaa Frequent Visitor
Frequent Visitor

Re: Inventory first in first out

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.

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 256 members 3,006 guests
Please welcome our newest community members: