Opening and Closing Stock Calculation with Purchase 1 & 2

[Pavan_123456789]

Power Bi File  Link :- https://drive.google.com/file/d/1seE1akFGXcAGRF40_WHnxQpzeWMSrWCT/view?usp=drive_link

Context:

✅I am calculating Opening Stock and Closing Stock for each year and item (Account.3), considering:

• Purchase Qty 1 — Primary stock to use for sales.

• Purchase Qty 2 — Backup stock if Purchase Qty 1 is exhausted.

  🔥🧩 Requirements:

•  

  ✅Opening_Stock1

  • Gets value from the previous year’s Closing_Stock1.

  • If no previous year’s data, it should be 0.
    --------------------------------------------------------------------------------------
    ✅Closing_Stock1

    • Formula:

    Closing_Stock1=Opening_Stock1+Purchase Qty 1+Sales Qty
    

    • If Sales Qty is more than available stock in Purchase Qty 1, the excess sales will be deducted from Purchase Qty 2.

    • --------------------------------------------------------------------------

      ✅Opening_Stock2

      • Gets value from the previous year’s Closing_Stock2.

      • If no previous year’s data, it should be 0.------------------------------------------------------------------------------------

      • Closing_Stock2

        • Deducts excess sales from Purchase Qty 2 after Purchase Qty 1 is exhausted.
          
          Expected Result 
          
          

          

          I tried this measures but not working one or two values not getting properly in opening stock1

          Opening_Stock 1=

          VAR PrevYear = MAX('Table'[Year]) - 1

          
          

          -- Find the latest available Closing Stock from previous years

          VAR PrevClosingStock =

              CALCULATE(

                  SUM('Table'[Purchase Qty 1]) - ABS(SUM('Table'[Sales Qty])),

                  FILTER(

                      ALL('Table'),  

                      'Table'[Year] <= PrevYear &&  -- Track all past years, not just one

                      'Table'[Account.3] = MAX('Table'[Account.3])

                  )

              )

          
          

          RETURN

              IF(ISBLANK(PrevClosingStock), 0, PrevClosingStock)
          
          

          Closing_Stock1 =

          VAR OpeningStock = [Opening_Stock]   -- Now using the corrected Opening Stock

          
          

          VAR SalesQty = SUM('Table'[Sales Qty])

          VAR PurchaseQty = SUM('Table'[Purchase Qty 1])

          
          

          RETURN OpeningStock + PurchaseQty + SalesQty

[v-kathullac]

Hi @Pavan_123456789 ,

Thanks @lbendlin for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

Hi @Pavan_123456789 ,

Thanks @lbendlin for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

Hi @Pavan_123456789 ,

Thanks @lbendlin for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Chaithanya.

[lbendlin]

Your sample data doesn't cover the scenarios

Purchase Qty1 is never exhausted.

Please provide more realistic sample data.

[Pavan_123456789]

@lbendlin  Thank you for your response.

To clarify, exhausted means that the closing stock should equal the sum of Purchase Qty1 and Sales Qty.
For example:

• In 2024, for Biala 1.5L:

  • Closing Stock = -100 (Sales Qty) + 80 (Purchase Qty1) = -20.

  • Since the value is negative, it indicates that Purchase Qty1 is exhausted, and the remaining 20 should be taken from Purchase Qty2.

The sample data provided is fine.

[lbendlin]

Since the value is negative, it indicates that Purchase Qty1 is exhausted, and the remaining 20 should be taken from Purchase Qty2.

I am not aware of a method in DAX to do conditional accumulation.  Your only option is List.Accumulate in Power Query.

[Pavan_123456789]

Hi @lbendlin 
Could you please suggest me how to do in power Query?

[lbendlin]

Here's the general idea. You would have to do similar code for all four columns.

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDS9UrM0zUyUdJR0jUyAJJOmYk5iQqGPkCmoYEBlIzViQbSSEoN8Ss1QzbVGJ9SIzMUU5GM1TMFqbZAUWyJpNgMn7FAf7mlJukamRLlL4RSQwwHGJqi+QzJXEI+Qyg1IeBYsLfM8DjWFDUSzHA4Fms0mKM7FofPsCvGVAqLMws0n2FzAyzKLHBGmTFIZSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Qty = _t, Account.3 = _t, #"Maspex Biala" = _t, #"Roust Biala" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Qty", Int64.Type}, {"Account.3", type text}, {"Maspex Biala", Int64.Type}, {"Roust Biala", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Maspex Biala", "Purchase Qty1"}, {"Roust Biala", "Purchase qty 2"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]),Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", { "Account.3","Year"}, {{"Purchase Qty 1", each List.Average([Purchase Qty1]), type nullable number}, {"Purchase Qty 2", each List.Average([Purchase qty 2]), type nullable number}, {"Sales Qty", each List.Sum([Qty]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Account.3", Order.Ascending}, {"Year", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(
  #"Sorted Rows", 
  "Open Stock 1", 
  (k) =>
    let
      years = Table.SelectRows(
        #"Sorted Rows", 
        each [Account.3] = k[Account.3] and [Year] < k[Year]
      )[Year]
    in
      List.Accumulate(
        years, 
        0, 
        (s, c) =>
          if List.Count(years) = 0 then
            s
          else 
            let
              curryear = Table.SelectRows(
                    #"Sorted Rows", 
                    each [Account.3] = k[Account.3] and [Year] = c 
                  ){0}
            in //List.Sum(curryear[Purchase Qty 1])
              if s + curryear[Purchase Qty 1] - curryear[Sales Qty] >= 0
              then s + curryear[Purchase Qty 1] - curryear[Sales Qty] 
              else s + curryear[Purchase Qty 1] + curryear[Purchase Qty 2] - curryear[Sales Qty] 
      )
)
in
    #"Added Custom1"