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
Anonymous
Not applicable

Calculate the days between inbound and outbound date according "FIFO" (First in, First out)

 

Hello

 

I am looking for some help in order to calculate the "days a product is kept in stock"

Below you find an example of the 2 data sets I have and want to combine : 

 

1. Inbound tabel

- A product code "A" & "B". This represent the articel code of my products. I don't have a unique identifier for each item of this producc/articel. 

- The date the product came into the stock 

- The quantity : this is currently always "1" (1 row = 1 product received at a specific date).

 

BjornClopterop_0-1626348593539.png

 

2. Outbound table : 

 

- A product code "A" & "B"

- Dat out : the date the product is sold

- Quantity : always 1, however it is possible an item is sold multiple times on the same day. If this is the case you have mutiple line. Example is product 1, sold on january 20th

BjornClopterop_3-1626348929304.png

 

The column in grey ned to be calculated according to the FIFO principle (first in, First out) 

(product A sold on January15th was kept in stock for 14 days (delta between 15/01 and 01/01) )

 

Any tips how o automate this via DAX (or is a solution within Power Query better? 

 

Thanks for the input and support 

 

Regards

Bjorn 

 

 

 

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

you need a index column.

I create a sample and you can take it for reference.  Please check the sample file I attached below.

 

Days in stock = 
var _valueAB=MIN(OUTBOUND[Product])
var _countAB=CALCULATE(COUNTROWS(OUTBOUND),FILTER(ALL(OUTBOUND),OUTBOUND[Index]<=MIN(OUTBOUND[Index])&& OUTBOUND[Product]==MIN(OUTBOUND[Product])))
var _get_IN_index=MAXX(TOPN(_countAB,FILTER(ALL(INBOUND),INBOUND[Product]==_valueAB),INBOUND[Index],ASC),INBOUND[Index])
var _get_IN_date=CALCULATE(MIN(INBOUND[Date In]),FILTER(ALL(INBOUND),INBOUND[Index]==_get_IN_index))
var _get_OUT_date=MIN(OUTBOUND[Date Out])
var _daysdiff=DATEDIFF(_get_IN_date,_get_OUT_date,DAY)
return _daysdiff

 

result

vxiaotang_0-1626947780920.png

 

 

 

Best Regards,

Community Support Team _ Tang

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

View solution in original post

1 REPLY 1
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

you need a index column.

I create a sample and you can take it for reference.  Please check the sample file I attached below.

 

Days in stock = 
var _valueAB=MIN(OUTBOUND[Product])
var _countAB=CALCULATE(COUNTROWS(OUTBOUND),FILTER(ALL(OUTBOUND),OUTBOUND[Index]<=MIN(OUTBOUND[Index])&& OUTBOUND[Product]==MIN(OUTBOUND[Product])))
var _get_IN_index=MAXX(TOPN(_countAB,FILTER(ALL(INBOUND),INBOUND[Product]==_valueAB),INBOUND[Index],ASC),INBOUND[Index])
var _get_IN_date=CALCULATE(MIN(INBOUND[Date In]),FILTER(ALL(INBOUND),INBOUND[Index]==_get_IN_index))
var _get_OUT_date=MIN(OUTBOUND[Date Out])
var _daysdiff=DATEDIFF(_get_IN_date,_get_OUT_date,DAY)
return _daysdiff

 

result

vxiaotang_0-1626947780920.png

 

 

 

Best Regards,

Community Support Team _ Tang

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

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.