cancel
Showing results for
Did you mean:
Frequent Visitor

## Stock Value Opening balance and Closing Balance

Hi all,

Very new to DAX so apologies if this is not too complex.

I am trying to write a report that values stock on a daily basis (see table below that I created in Excel to demonstrate issue)

The only data I have are Purchase units/ Purchase \$/ Sold Units/ Sold \$

 Stock Item # Opening Balance (Units) Opening Balance \$ Purchase (Units) Purchase \$ Sold (Units) CoS \$ Closing Balance (Units) Closing Balance (\$) =+I3 =+J3 =IFERROR((F4+D4)/(C4+E4)*G4,0) =C4+E4+G4 =+D4+F4+H4 01/01/2021 1 0 0.00 0 0.00 0 0.00 0.00 0.00 02/01/2021 1 0 0.00 0 0.00 0 0.00 0.00 0.00 03/01/2021 1 0 0.00 38 41.80 3 3.30 41.00 45.10 04/01/2021 1 41 45.10 40 32.00 3 2.86 84.00 79.96 05/01/2021 1 84 79.96 14 21.00 3 3.09 101.00 104.05 06/01/2021 1 101 104.05 39 66.30 4 4.87 144.00 175.21 07/01/2021 1 144 175.21 33 39.60 3 3.64 180.00 218.45 08/01/2021 1 180 218.45 29 31.90 3 3.59 212.00 253.95

I am happy with the calculation for the units and can get opening and closing balances by using a filter on date.

The problem is when I come to work out closing balance value for one day and the opening balance value for the next. As the Closing Balance is valued as (Opening \$ + Purchase \$ - Cost of Sales \$) and the Opening balance is reliant on the Closing Balance column I am getting a Circular error. (This is easy to overcome as I can reference rows to avoid circular errors).

I have been scratching my head for ages trying to see ways of overcoming this so any help would be greatly appreciated.

Many thanks for talking time to read this.

Andrew

10 REPLIES 10
Frequent Visitor

I have similar problem. For the stock amount I can calculate from cumulative intial, input and output , then i can find closing amount and i can calculate opening amount. But for the cost i can not. If you calculate cumulative purchase and cumulative CoS \$...still not working. Because CoS \$ depends the price of your purchase cost...It changes month to month, also CoS \$ per sold changes.

Filter, calculate, write the close balance to a table is not working... Change relations about dates not working.

Somehow opening balace must take the previous month closing balance without dependency.

My problem is;

https://community.powerbi.com/t5/Desktop/Cost-Calculation-Dependency/m-p/1843658#M712994

Community Support

According to the information you provided, I created three calculated columns in Powerbi Desktop in accordance with the requirements of the formula in the Execl table, and successfully obtained the required results, and there was no circular dependency error result. Refer to the following:

``````Column_CoS =
IF (
'Table'[Sold (Units)] = 0,
0,
( ( 'Table'[Opening Balance \$] + 'Table'[Purchase \$] ) / ( 'Table'[Opening Balance (Units)] + 'Table'[Purchase (Units)] ) ) * 'Table'[Sold (Units)]
)``````
``Column_Closing balance = 'Table'[Opening Balance (Units)]+'Table'[Purchase (Units)]+'Table'[Sold (Units)]``
``Column_Closing Balance\$ = 'Table'[Opening Balance \$] + 'Table'[Purchase \$] +'Table'[Column_CoS]``

Below is the sample pbix file.

Best Regards,
Henry

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

Frequent Visitor

Thanks @v-henryk-mstf

One question - how did you calculate the Opening Balance \$?

Many thanks,
Andrew

Frequent Visitor

Hi @@v-henryk-mstf

Thanks for getting back to me - the issue (as I see it) is as follows:

On day 3/1/21, there is zero opening balance, purchases of \$45.10 (I see there is an error in the spreadsheet but just assume here that the purchases are \$45.10), there are no sales therefore the closing balance would be \$45.10

On the 4/1/21 The opening balance needs to take the 3/1/21's closing balance of \$45.10. Then to get to the closing balance on the 1/4/21 you would take the opening balance, add purchase \$  and subtract Cost of sales (Cost of sales = Units sold x (Opening \$+Purch \$)/(Opening units+Purchase units).

The issue is that Opening balance on one day is dependant on Closing balance from the previous day and Closing balance is dependant on Opening balance of the same day. In Excel this is not a problem as you can point to rows and avoid the circular error but in DAX I am looking at columns so Opening balance column is dependant on Closing balance column which is dependant on Opening balance column. Hense the circular error.

I could do this in Excel with formulas but the issue is the dataset is > 1 million rows so using Excel formulas is a problem.

Any help is greatly appreciated.

Andrew

Community Support

According to your needs, I did the following test: Create a column to obtain the sum value of the two fields Purchase\$ and CoS\$. Then create a measure to accumulate it according to the date to get the same result as the Closing Balance (\$) field in excel.

``Col_S = 'Table'[Purchase \$]+'Table'[CoS \$]``
``````M =
VAR cur_day =
SUM ( 'Table'[Data] )
RETURN
CALCULATE (
SUM ( 'Table'[Col_S] ),
FILTER ( ALL ( 'Table' ), 'Table'[Data] <= cur_day )
)``````

Below is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

Frequent Visitor

The issue is that I don't have CoS (which would need Opening balance \$ to calculate).

I would have attached a pbix but cannot figure out how to upload a file.

I have attached a pbix with two tables:

Data - this is the raw data

Result: - this is what I am looking to end up with

I also included what I have done so far (which is not much really, but I can get Opening an Closing balances (Units) working.

Once again - thank you

Frequent Visitor
Frequent Visitor

I read and analyze the code, you sold the product always same price 1,1 USD/# but it changes day by day according to your purcase unit cost

Community Support

Do you mean the Circular error that occurred when the calculated column was created in Powerbi desktop? Can you further clarify your needs, the meaning of each lot and the desired result. I will answer you as soon as possible.

Best Regards,
Henry

Frequent Visitor

I have re added the picture

Announcements