Forum Discussion

Naxyr's avatar
Naxyr
New Member
6 years ago
Solved

Need help Getting LOT Numbers based of LIFO

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

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Icey's avatar
    Icey
    5 years ago

    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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      yavuzonaran
      Frequent Visitor
      3 years ago

      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