cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dennis_Pedersen Frequent Visitor
Frequent Visitor

FIFO Stock

Hi 

 

Have been looking at some posts and cannot find the solution for this. I have a purchase fact table like below

If the UnitsInStock is 800, I need a DAX measure for calculation Qty_Stock like below?

 

By the way the UnitsInStock can differ to my selection of date. (see measure below)

 

Is there a possible solution for this?

 

 

Itemnumber Date                 QTY bought  Qty_Stock

23                 20-06-2019                600               600

23                 18-05-2019                  50                 50

23                 23-04-2019              1000               150

 

 

UnitsInStock:=
IF (
MIN ( 'DIM_Time'[DateValue] ) <= CALCULATE ( MAX ( 'Fact_Inventory'[PostingDate] ); ALL ( 'Fact_Inventory' ) );
CALCULATE (
SUM ( 'Fact_Inventory'[Quantity] );
FILTER (
ALL( 'DIM_Time'[DateValue]);
'DIM_Time'[DateValue] <= MAX ( 'DIM_Time'[DateValue] )
)
)
)

 

5 REPLIES 5
Super User
Super User

Re: FIFO Stock

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.

 

Thanks.

 

Best

Darek

Dennis_Pedersen Frequent Visitor
Frequent Visitor

Re: FIFO Stock

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 Frequent Visitor
Frequent Visitor

Re: FIFO Stock

Has anybody had this challenge and found a solution?

Highlighted
Super User
Super User

Re: FIFO Stock

Jee... Mate, please read this:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

If you don't get answers, is it not clear enough that people don't understand your problem?

Best
D.
Dennis_Pedersen Frequent Visitor
Frequent Visitor

Re: FIFO Stock

Okay I will try to elaborate.

 

Itemnumber Date                 QTY bought  Qty_Stock

23                 20-06-2019                600               600

23                 18-05-2019                  50                 50

23                 23-04-2019              1000               150

 

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).

 

Now in another fact table(which also include sale) I calculate units in stock on a certain date with the measure below.

 

Now if we imagine that 30-06-2019 there is 800 left of the 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)

 

How can I make a measure which returns the following from my purchase tabel?

 

Itemnumber Date                 QTY bought  Qty_Stock

23                 20-06-2019                600               600

23                 18-05-2019                  50                 50

23                 23-04-2019              1000               150

 

UnitsInStock:=
IF (
MIN ( 'DIM_Time'[DateValue] ) <= CALCULATE ( MAX ( 'Fact_Inventory'[PostingDate] ); ALL ( 'Fact_Inventory' ) );
CALCULATE (
SUM ( 'Fact_Inventory'[Quantity] );
FILTER (
ALL( 'DIM_Time'[DateValue]);
'DIM_Time'[DateValue] <= MAX ( 'DIM_Time'[DateValue] )
)
)
)

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,531)