cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## FEFO Violation

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.

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.

1 ACCEPTED SOLUTION
Community Support

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

4 REPLIES 4
Community Support

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

Helper I

Many thanks.

Super User

@dearears , A new column

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

Helper I

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.