FIFO Stock

Question

Hi&nbsp;&nbsp;Have been looking at some posts and cannot find the solution for this. I have a purchase fact table like belowIf the UnitsInStock is 800, I need a DAX measure for calculation Qty_Stock like below?&nbsp;By the way&nbsp;the UnitsInStock can differ to my selection of date. (see measure below)&nbsp;Is there a possible solution for this?&nbsp;&nbsp;Itemnumber Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QTY bought&nbsp; Qty_Stock23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20-06-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 600&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;18-05-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23-04-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;150&nbsp;&nbsp;UnitsInStock:=IF (MIN ( 'DIM_Time'[DateValue] ) &lt;= CALCULATE ( MAX ( 'Fact_Inventory'[PostingDate] ); ALL ( 'Fact_Inventory' ) );CALCULATE (SUM ( 'Fact_Inventory'[Quantity] );FILTER (ALL( 'DIM_Time'[DateValue]);'DIM_Time'[DateValue] &lt;= MAX ( 'DIM_Time'[DateValue] ))))&nbsp;

anonymous · Answer

Please formulate your question in a very clear fashion so that everyone (not only you) can understand it. Then maybe someone will be able to help you.&nbsp;Thanks.&nbsp;BestDarek

anonymous · Answer

Jee... Mate, please read this:https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490If you don't get answers, is it not clear enough that people don't understand your problem?BestD.

dennis_pedersen · Answer

Okay - if I for example have chosen today(from DIM_time) the UnitsInStock measure may give me a figure of 800 pcs. in Stock.

I would like to split this figure into 3 purchases according to my fact Purchase table like the column at the right.

Is that possible?

dennis_pedersen · Answer

Has anybody had this challenge and found a solution?

dennis_pedersen · Answer

Okay I will try to&nbsp;elaborate.&nbsp;Itemnumber Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QTY bought&nbsp; Qty_Stock23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20-06-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 600&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;18-05-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23-04-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;150&nbsp;As you can see from my Purchase_table, I have an item (no. 23), which has been purchased 3 times.The total QTY bought is 1650(1000+50+600).&nbsp;Now in another fact table(which also include sale) I calculate units in stock on a certain date with the measure below.&nbsp;Now if we imagine that 30-06-2019 there is 800 left of the&nbsp;total QTY bought(1650). According to FIFO this is 600 from 20-06-2019, 50 from 18-05-2019 and 150 from 23-04-2019(1000 was bought, 850 of this has been sold afterwards)&nbsp;How can I make a measure which returns the following from my purchase tabel?&nbsp;Itemnumber Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QTY bought&nbsp; Qty_Stock23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20-06-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 600&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;18-05-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23-04-2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;150&nbsp;UnitsInStock:=IF (MIN ( 'DIM_Time'[DateValue] ) &lt;= CALCULATE ( MAX ( 'Fact_Inventory'[PostingDate] ); ALL ( 'Fact_Inventory' ) );CALCULATE (SUM ( 'Fact_Inventory'[Quantity] );FILTER (ALL( 'DIM_Time'[DateValue]);'DIM_Time'[DateValue] &lt;= MAX ( 'DIM_Time'[DateValue] ))))

Forum Discussion

FIFO Stock

5 Replies

Related content

How to Calculate Stock Aging Using FIFO Method

FIFO - Stock Calculation

How to Calculate Stock Aging Using FIFO Method

Power Qurey for FIFO Stock Calculation

Inventory and Stock balance calculation