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
Highlighted
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?

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.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,550)