Forum Discussion

daowei's avatar
daowei
Regular Visitor
1 year ago

inventory aging buckets with FIFO

Dear all,

 

I have been struggling with a caculation for a few days, but cannot find a way to get around the circular dependency error.  

PurchaseSaleAge Corrected Stock at the begining of each week  (Inc. Discard)
  1w2w3w4w5w6w (Discard)
20002502000000002000
 5500175000001750
 5000012000001200
1750250175000700002450
 250017500045002200
 775001750002001950
1750350175000975002725
350075350017500062505875
 900035001750005505800
1500450150003500850005850
 1325015000350040005400
30001503000015000257507075
27506252750300001500024259675
500011755000275030000875011625
3500282535005000275027000013950

Here is a example of aging buckets I created in Excel, based on four assumptions

1. Before simulation we have the values in first row, start with 2000 in 1 week aging bucket.

2. The aging bucket display number of stock at the begining of each week. 

3. First in first out

4. Any item older than 6 weeks will be discarded, therefore in the next week unused item in week 6 will be zero. 

 

In Excel it's easy to caculate remaining units from last week for deduction, but in PowerBI caculated column I keep getting circular dependency error, as I try to sumx previous values. Any suggestions on how to solve this problem?

Need Help

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    daowei To avoid circular dependency errors in Power BI when calculating inventory aging buckets with FIFO, you can use measures instead of calculated columns.

     

    Ensure you have a table with your purchase and sale data, including the week number.

     

    Calculate the total purchases for each week.

         Total Purchases = SUM('Inventory'[Purchase])
     
    Calculate the total sales for each week.
    Total Sales = SUM('Inventory'[Sale])
     
    DAX
    Stock at Beginning of Week =
    VAR CurrentWeek = MAX('Inventory'[Week])
    VAR PreviousWeekStock =
    CALCULATE(
    [Stock at End of Week],
    FILTER(
    'Inventory',
    'Inventory'[Week] = CurrentWeek - 1
    )
    )
    RETURN
    IF(
    ISBLANK(PreviousWeekStock),
    [Total Purchases],
    PreviousWeekStock
    )
     
    Stock at End of Week =
    [Stock at Beginning of Week] + [Total Purchases] - [Total Sales]
     
    Stock 1 Week =
    CALCULATE(
    [Stock at End of Week],
    FILTER(
    'Inventory',
    'Inventory'[Week] = MAX('Inventory'[Week]) - 1
    )
    )
     
    Discarded Stock =
    CALCULATE(
    [Stock at End of Week],
    FILTER(
    'Inventory',
    'Inventory'[Week] <= MAX('Inventory'[Week]) - 6
    )
    )

     

    • daowei's avatar
      daowei
      Regular Visitor
      1 year ago

      Hello bhanu_gautam,

       

      I've tried to create measures with your methods, but Powerbi report a circular dependency between [stock at end of week] and [stock at beginning of week] again... 

      • bhanu_gautam's avatar
        bhanu_gautam
        Super User
        1 year ago

        daowei , 

         

        Stock at Beginning of Week =
        VAR CurrentWeek = MAX('Inventory'[Week])
        VAR PreviousWeekStock =
        CALCULATE(
        SUMX(
        FILTER(
        'Inventory',
        'Inventory'[Week] = CurrentWeek - 1
        ),
        [Total Purchases] - [Total Sales]
        )
        )
        RETURN
        IF(
        ISBLANK(PreviousWeekStock),
        [Total Purchases],
        PreviousWeekStock
        )

         

        and DAX
        Stock at End of Week =
        VAR CurrentWeek = MAX('Inventory'[Week])
        RETURN
        CALCULATE(
        [Stock at Beginning of Week] + [Total Purchases] - [Total Sales],
        FILTER(
        'Inventory',
        'Inventory'[Week] = CurrentWeek
        )
        )