topic Re: Opening and Closing Stock Calculation with Purchase 1 & 2 in DAX Commands and Tips

Opening and Closing Stock Calculation with Purchase 1 & 2

Pavan_123456789 — Fri, 28 Mar 2025 16:48:54 GMT

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

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

lbendlin — Fri, 28 Mar 2025 17:57:29 GMT

Your sample data doesn't cover the scenarios

Purchase Qty1 is never exhausted.

Please provide more realistic sample data.

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Pavan_123456789 — Mon, 31 Mar 2025 09:10:35 GMT

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.

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

lbendlin — Mon, 31 Mar 2025 14:13:01 GMT

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.

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Pavan_123456789 — Tue, 01 Apr 2025 14:55:33 GMT

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

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

lbendlin — Wed, 02 Apr 2025 12:04:24 GMT

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"

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

v-kathullac — Mon, 07 Apr 2025 11:55:58 GMT

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.

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

v-kathullac — Fri, 11 Apr 2025 11:41:57 GMT

Hi @Pavan_123456789 ,

Thanks @lbendlin for Addressing the issue.

Regards,
Chaithanya.

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

v-kathullac — Thu, 17 Apr 2025 16:03:43 GMT

Hi @Pavan_123456789 ,

Thanks @lbendlin for Addressing the issue.

Regards,
Chaithanya.

topic Re: Opening and Closing Stock Calculation with Purchase 1 & 2 in DAX Commands and Tips

Opening and Closing Stock Calculation with Purchase 1 & 2

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

Context:

🔥🧩 Requirements:

✅Opening_Stock1

✅Opening_Stock2

Closing_Stock2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

topic Re: Opening and Closing Stock Calculation with Purchase 1 & 2 in DAX Commands and Tips

Opening and Closing Stock Calculation with Purchase 1 & 2

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

🔥🧩 Requirements:

✅Opening_Stock1

✅Opening_Stock2

Closing_Stock2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

Re: Opening and Closing Stock Calculation with Purchase 1 & 2

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

Context: