Forum Discussion

saranicole2695's avatar
saranicole2695
Frequent Visitor
5 years ago

Determine Current and Projected FIFO Cost Layers

Hello!

 

I am working with a dataset that has a Purchases Table and a Contract Sales Table. The business uses the FIFO costing method. What I am trying to do is find the current cost layer (for each product the cost associated with the smallest sequence number) and then the "next" or projected cost layer. Since the sales haven't taken place yet, these are orders to be completed at some point in the future, the cost layers haven't been releived as that doesn't happen until a sale in fully invoiced. (Take the sales data and "relieve" the appropriate cost layers and determine the new cost where remaining > 0 and the smallest sequence number. Below are some sample data tables and what I am would expect the results to be. I am going to use this data in a card visual on a report.

 

Thanks!!

 

Of note: When a sale is invoiced, the transaction relieves the cost layer and is removed from the sales table.

 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi saranicole2695 ,

    You can create two measures as below to get the current cost and "next" cost:

    Current cost = 
CALCULATE (
    MAX ( 'Purchases'[Cost] ),
    FILTER (
        'Purchases',
        'Purchases'[Product] = MAX ( 'Purchases'[Product] )
            && 'Purchases'[Seq] = MIN ( 'Purchases'[Seq] )
    )
)​
    Next cost = 
VAR _maxsdate =
    CALCULATE (
        MAX ( 'Contract Sales'[Sales Date] ),
        ALLEXCEPT ( 'Contract Sales', 'Contract Sales'[Product] )
    )
VAR _minseq =
    CALCULATE (
        MIN ( 'Purchases'[Seq] ),
        FILTER (
            'Purchases',
            'Purchases'[Product] = MAX ( 'Purchases'[Product] )
                && 'Purchases'[Qty Remaining] > 0
                && 'Purchases'[Date] >= _maxsdate
        )
    )
RETURN
    CALCULATE (
        MIN ( 'Purchases'[Cost] ),
        FILTER (
            'Purchases',
            'Purchases'[Product] = MAX ( 'Purchases'[Product] )
                && 'Purchases'[Seq] = _minseq
        )
    )

    If the above one can't get the correct results, please provide the calculation logic of next cost. Namely, why the next cost for product A is get the cost from seq 962 and product B is get the cost from seq 766? Thank you.

    Best Regards

    Rena

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi saranicole2695  ,

    Whether the problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

    Best Regards

    Rena