FEFO Violation

[dearears]

Hi,

Trying to put a flag ("X") on Articles that were shipped violating FEFO (First Expiry First Out)

Logic: shipment violated FEFO if for the article, expiry date on shippment is earlier than expiry date of shipments previously made.

Thanks in advance.

Table:

Article	Expiry date	Shipped On	Violation Flag
A	01.01.2020	01.02.2020
A	01.02.2020	05.02.2020
A	01.01.2020	06.02.2020	x
A	01.04.2020	08.02.2020
B	01.01.2020	01.02.2020
B	01.02.2020	05.02.2020
B	01.01.2020	06.02.2020	x
B	01.04.2020	08.02.2020

Example:

I am about to make a shipment today. (18.03.2022).

I choose to ship article A with expiry date of 1 Jan 2021.

FEFO is violated if before today, a batch of Article A was shipped with an expiry date more than 1 Jan 2021.

Or in other words, FEFO is met, if batches shipped before today were shipped with expiry date before 1 Jan 2021.

[v-yangliu-msft · Accepted solution]

Hi  @dearears ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

Violation Flag =
var _current=CALCULATE(MAX('Table'[Expiry date]),FILTER(ALL('Table'),'Table'[Article]=EARLIER('Table'[Article])&&'Table'[Index]=EARLIER('Table'[Index])))
var _Before=CALCULATE(MAX('Table'[Expiry date]),FILTER(ALL('Table'),'Table'[Article]=EARLIER('Table'[Article])&&'Table'[Index]=EARLIER('Table'[Index])-1))
return
IF(
    _current<_Before,"x",BLANK())

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[dearears]

Many thanks. 

[amitchandak]

@dearears , A new column

if([Expiry date] <[Shipped on] , 1, blank() )

[dearears]

Hi @amitchandak , Wish it was that simple 😀 This is not what I was after. Let me rephrase with an example.

I am about to make a shipment today. (18.03.2022).

I choose to ship article A with expiry date of 1 Jan 2021.

FEFO is violated if before today, a batch of Article A was shipped with an expiry date more than 1 Jan 2021.

Or in other words, FEFO is met, if batches shipped before today were shipped with expiry date before 1 Jan 2021.