Forum Discussion

nathsonam13's avatar
nathsonam13
Frequent Visitor
1 year ago
Solved

Opening Stock & Closing Stock Calculation

I am trying to calculate Opening Stock and Closing Stock for SKUs on a daily basis, but I keep encountering a circular dependency error when referencing previous day’s closing stock as the next day’s opening stock.

Data Details

I have a SKU_Date_Mapping table with:

  • SKU (Product ID)
  • Date (Daily records)
  • New Arrival, HL (New stock received)
  • Actual Sales (Sales for the day)
  • Closing Stock (Needs to be calculated)

I also have an Opening table with:

  • Ref SKUCode (Maps to SKU)
  • Date (Only first day of each month)
  • Opening Total KHL (Opening stock for the month)

Logic Required

  1. Opening Stock (Open'HL)

    • If it's the 1st of the month, use the value from the Opening table.
    • Otherwise, use the previous day's Closing Stock.

  2. Closing Stock Calculation

    • Closing Stock = Opening Stock + New Arrival - Actual Sales

Issue

Since Open'HL references Closing Stock, and Closing Stock depends on Open'HL, I am getting a circular dependency error.

How can I correctly calculate these values without a circular dependency? I cannot use Power Query as this is a calculated table.

Any suggestions would be appreciated!

"DAX " "DAX help" 'Need help'
"MEASURE"
"Power BI"
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi nathsonam13 ,

     

    Thanks Fowmy for the quick reply. I have some other methods to add:

    (1) This is my test data.

    (2) Create two columns.

    Closing = 

var _min_open=CALCULATE(SUM('Table'[Open 1st]),FILTER('Table',DAY([Date])=1 && [SKU]=EARLIER('Table'[SKU])))
var _sum_prod=CALCULATE(SUM('Table'[Prod]),FILTER('Table',[Date]<=EARLIER('Table'[Date]) && [SKU]=EARLIER('Table'[SKU])))
var _sum_sales=CALCULATE(SUM('Table'[Sales]),FILTER('Table',[Date]<=EARLIER('Table'[Date]) && [SKU]=EARLIER('Table'[SKU])))
RETURN IF(DAY([Date])=1,[Open 1st]+[Prod]-[Sales],_min_open+_sum_prod-_sum_sales)
    Open = 
var _open=CALCULATE(SUM('Table'[Closing]),FILTER('Table',[SKU]=EARLIER('Table'[SKU]) && [Date]=EARLIER('Table'[Date])-1))
RETURN IF(DAY([Date])=1,[Open 1st],_open)

    (3) Then the result is as follows.

     

     

    Best Regards,

    Neeko Tang

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

4 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    1 year ago

    nathsonam13 
    Can you share some sample data with the desired output to have a clear understanding of your question?
    Mention whether you want a calculated column or measure.
    You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

    • nathsonam13's avatar
      nathsonam13
      Frequent Visitor
      1 year ago

      Hi, 

      Thanks for replying. 

      below is the data 

      So, basically 1st Open is getting derived from Opening table as I mentioned so its fixed but now i want derived opening for each date and each sku so ideally 1st Closing will be 2nd opening but if i am trying this in Bi it is throwing circular reference error. 


      For your reference below are my current calculated col

      1st Open'HL =
      VAR CurrentSKU = 'OOS_KPI'[SKU]
      VAR CurrentDate = 'OOS_KPI'[Date]

      RETURN
      SUMX(
          FILTER(
              Opening,
              Opening[Ref SKUCode]= CurrentSKU &&
              Opening[Date] = CurrentDate
          ),
          'Opening'[Opening Total KHL]*1000
      )

       

      Actual Sales =
      VAR CurrentSKU = 'OOS_KPI'[SKU]
      VAR CurrentDate = 'OOS_KPI'[Date]

      RETURN
      SUMX(
          FILTER(
              'Primary & Production',
              'Primary & Production'[SKU] = CurrentSKU &&
              'Primary & Production'[Posting Date] = CurrentDate &&
              'Primary & Production'[Data Type] = "Primary"
          ),
          'Primary & Production'[Data in KHL]*1000
      )


      And below is production.

      New Arrival, HL =
      VAR CurrentSKU = 'OOS_KPI'[SKU]
      VAR CurrentDate = 'OOS_KPI'[Date]

      RETURN
      SUMX(
          FILTER(
              'Primary & Production',
              'Primary & Production'[SKU] = CurrentSKU &&
              'Primary & Production'[Posting Date] = CurrentDate &&
              'Primary & Production'[Data Type] = "Production"
          ),
          'Primary & Production'[Data in KHL]*1000
      )



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi nathsonam13 ,

     

    Thanks Fowmy for the quick reply. I have some other methods to add:

    (1) This is my test data.

    (2) Create two columns.

    Closing = 

var _min_open=CALCULATE(SUM('Table'[Open 1st]),FILTER('Table',DAY([Date])=1 && [SKU]=EARLIER('Table'[SKU])))
var _sum_prod=CALCULATE(SUM('Table'[Prod]),FILTER('Table',[Date]<=EARLIER('Table'[Date]) && [SKU]=EARLIER('Table'[SKU])))
var _sum_sales=CALCULATE(SUM('Table'[Sales]),FILTER('Table',[Date]<=EARLIER('Table'[Date]) && [SKU]=EARLIER('Table'[SKU])))
RETURN IF(DAY([Date])=1,[Open 1st]+[Prod]-[Sales],_min_open+_sum_prod-_sum_sales)
    Open = 
var _open=CALCULATE(SUM('Table'[Closing]),FILTER('Table',[SKU]=EARLIER('Table'[SKU]) && [Date]=EARLIER('Table'[Date])-1))
RETURN IF(DAY([Date])=1,[Open 1st],_open)

    (3) Then the result is as follows.

     

     

    Best Regards,

    Neeko Tang

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

    • nathsonam13's avatar
      nathsonam13
      Frequent Visitor
      1 year ago

      Hi Anonymous ,

      Thanks for this. It really worked. TT