Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
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
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
Solved! Go to Solution.
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
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.
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |