Find productions that reaches current inventory FIFO

[yavuzonaran]

Hello

I have to tables as below. I want to find production days of current inventory as we using fifo method and find inventory age with today() - production date. We assume today as 10.07.2023

Table 1-Current Inventory

Item1 100pcs

Item2 50pcs

Table 2-Productions

Item1 50pcs 01.07.2023

Item1 30pcs 03.07.2023

Item1 60pcs 07.07.2023

Item2 20pcs 02.07.2023

Item2 30pcs 04.07.2023

Item2 5pcs 08.07.2023

Expected result is;

Item1 60pcs 07.07.2023 3days

Item1 30pcs 03.07.2023 7days

Item1 10pcs 01.07.2023 9days

Item2 5pcs 08.07.2023 2days

Item2 30pcs 04.07.2023 6days

Item2 15pcs 02.07.2023 8days

Weighted average aging

Item1 100pcs 6,89days

Item2 50pcs 6,20days

Thanks in advance

Item	Quantity
Item1	100
Item2	50

Item	Quantity	Production Date
Item1	50	1.07.2023
Item1	30	3.07.2023
Item1	60	7.07.2023
Item2	20	2.07.2023
Item2	30	4.07.2023
Item2	5	8.07.2023

[tamerj1]

Hi @yavuzonaran 
Not sure what exactly do you mean by weighted average.

[yavuzonaran]

Hi

Thanks for your reply

First i need to do below one. This is the aging detail of inventory in hand

Expected result is;

Item1 60pcs 07.07.2023 3days

Item1 30pcs 03.07.2023 7days

Item1 10pcs 01.07.2023 9days

Total 100pcs

Item2 5pcs 08.07.2023 2days

Item2 30pcs 04.07.2023 6days

Item2 15pcs 02.07.2023 8days

Total 50pcs

[tamerj1]

@yavuzonaran 
Still not sure what are you trying to achieve

[yavuzonaran]

Thank you, i added as measure and it worked.

But i also need weighted average days. let me explain below, i hope this is clear to understand what i try to achieve

Item      Qty   Days

Item1    10     9

Item1    30     7

Item1    60     3

(10x9+30x7+60x3)/(10+30+60)=

480/100=4,80 weighted average days

[tamerj1 · Accepted solution]

@yavuzonaran 
Please refer to attached sample file with the proposed solution

Quantity = 
VAR CurrentDate = MAX ( Productions[Date] )
VAR CumulativeQty = 
    SUMX ( 
        FILTER ( 
            CALCULATETABLE ( Productions, ALL ( Productions ), VALUES ( 'Current Inventory'[Item] ) ),
            Productions[Date] >= CurrentDate
        ),
        Productions[Qty]
    ) 
VAR CurrentQty = SUM ( 'Current Inventory'[Qty] )
VAR ProducedQty = SUM ( Productions[Qty] )
RETURN 
    IF ( 
        NOT ISEMPTY ( Productions ),
        IF ( 
            HASONEVALUE ( Productions[Date] ),
            IF ( 
                CumulativeQty < CurrentQty, 
                ProducedQty, 
                CurrentQty + ProducedQty - CumulativeQty 
            ),
            CurrentQty
        )
    )

Average Days = 
IF ( 
    HASONEVALUE ( Productions[Date] ),
    AVERAGEX (
        Productions,
        DATEDIFF ( Productions[Date], TODAY ( ), DAY ) + 1
    ),
    SUMX (
        Productions,
        DIVIDE ( 
            [Quantity] * ( DATEDIFF ( Productions[Date], TODAY ( ), DAY ) + 1 ),
            RELATED ( 'Current Inventory'[Qty] )
        )
    )
)

[WinterMist]

@tamerj1 

Wondering if you could educate me once again....this time, about the VAR [CumulativeQty].

In my mind:

- ALL (Productions)    --REMOVES ALL FILTERS FROM TABLE 'Productions'

- VALUES ('Current Inventory'[Item])   --ADDS A FILTER FOR THE [Item] IN THE CURRENT FC.

IF a filter for the current item (e.g. Item1) is added, then the FC in the table visual for rows under Item1 should only relate to the 3 dates that apply to Item 1 in the table data:

- July 1

- July 3

- July 7

NOTE: The other 3 dates ( July 2, 4 & 8 ) apply to Item2 & should not appear in the Item1 FC.

However, if I return the VAR [CumulativeQty], the other 3 dates (for Item2) actually appear in the table visual within the Item1 FC (Filter Context).

This greatly confuses me.

I realize this does not impact your end result query as you specifically ignore where Productions ISEMPTY within your RETURN clause.  So my q isn't really how you are handling it.  My q is more of: Shouldn't your VALUES clause have handled it already???

I can override this by creating the VAR [ProductionsExist] (commented out in the screenshot above), but this is again the equivalent of what you are doing in your IF logic, and again seems like it should not be necessary.

Regards,

Nathan

[yavuzonaran]

https://file.io/A9a3bOpQv4Cl

updated file to show errors. could you please check?

[tamerj1]

@yavuzonaran 
So negatives should be zero?

[yavuzonaran]

Hello again,

I applied solution to my raw data, and there is a little problem need to be solved. 

Problem

I think this expression causing this. I need a correction for turning negative

[yavuzonaran]

I also find out another problem like this below

because of edge value  

produced 31.152 pcs at 27.05.2023 but current inventory is only 3.516 pcs.

its been only 43 days since produced but measure calculates

31.152/3.516x43=380,98

correct formula must calculate

3.516/3.516x43=43

[yavuzonaran]

This is exactly what i try to do! Thanks a lot 🙏