cancel
Showing results for
Did you mean:
Highlighted
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. Is it possible to do something similar in Power BI? I have googled a lot but still have no idea how I should start.

1 ACCEPTED SOLUTION

Accepted Solutions 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 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

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.
2 REPLIES 2 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 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

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.
Cynwaa Frequent Visitor

## Re: Inventory first in first out

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.

Announcements Learn the answers to some of the questions asked during the Amanda Triple A event. #### 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.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 256 members 3,006 guests
Recent signins:
• k_rolling • lux_sgupta • jpattamthanam • psembaj • mmassey1010 • gdecome • rheimbr • Aymensabri • Wrzesien • lliu08 • Phantomiceman • emartinez1 • claireli • shadfield123 