Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

 

Thanks in advance.

 

Table:

ArticleExpiry dateShipped OnViolation Flag
A01.01.202001.02.2020 
A01.02.202005.02.2020 
A01.01.202006.02.2020x
A01.04.202008.02.2020 
B01.01.202001.02.2020 
B01.02.202005.02.2020 
B01.01.202006.02.2020x
B01.04.202008.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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Many thanks. 

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , A new column

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.