Need help Getting LOT Numbers based of LIFO

[Naxyr]

I Have got 2 Tables 
1 Is having Stock In hand 

Code
SIH ( Qty)

2 is having batch Wise Goods Receipts 
Code 
Date Received 
Qty

Lot Number 
Exp Date

Want to allocate Balance SIH (Last In First Out) and Show the Lot Numbers 

I want to get only the latest lot numbers which reach the qty and hide all other Lot Numbers

[Icey · Accepted solution]

Hi @Naxyr ,

Please check:

Measure =
VAR RunningTotal =
    CALCULATE (
        SUM ( 'Table 2'[QTY] ),
        FILTER (
            ALLSELECTED ( 'Table 2' ),
            'Table 2'[CODE] = MAX ( 'Table 2'[CODE] )
                && 'Table 2'[Expiration Date] >= MAX ( 'Table 2'[Expiration Date] )
        )
    )
VAR RunningTotal_ =
    CALCULATE (
        SUM ( 'Table 2'[QTY] ),
        FILTER (
            ALLSELECTED ( 'Table 2' ),
            'Table 2'[CODE] = MAX ( 'Table 2'[CODE] )
                && 'Table 2'[Expiration Date] > MAX ( 'Table 2'[Expiration Date] )
        )
    )
VAR SIH_ =
    SUM ( 'Table 1'[SIH] )
RETURN
    IF (
        HASONEVALUE ( 'Table 2'[Expiration Date] ),
        MAX (
            IF ( RunningTotal <= SIH_, SUM ( 'Table 2'[QTY] ), SIH_ - RunningTotal_ ),
            BLANK ()
        ),
        SUM ( 'Table 1'[SIH] )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yavuzonaran]

This works thanks a lot!

I wonder if its possible to calculate in data table?

I also would like to calculate weighted average # of days by multiplying today-prod. date and qty.

Thanks in advance

[Greg_Deckler]

@Naxyr - Not super clear. 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.