Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cynwaa
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
v-lili6-msft
Community Support
Community Support

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
v-lili6-msft
Community Support
Community Support

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.