cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrewhaicalis
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/2021100.0000.0000.000.000.00
02/01/2021100.0000.0000.000.000.00
03/01/2021100.003841.8033.3041.0045.10
04/01/202114145.104032.0032.8684.0079.96
05/01/202118479.961421.0033.09101.00104.05
06/01/20211101104.053966.3044.87144.00175.21
07/01/20211144175.213339.6033.64180.00218.45
08/01/20211180218.452931.9033.59212.00253.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
mehmet
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

 

 

v-henryk-mstf
Community Support
Community Support

Hi @andrewhaicalis ,

 

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]

v-henryk-mstf_0-1618538898064.png

 

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.

Thanks @v-henryk-mstf 

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

Many thanks,
Andrew

andrewhaicalis
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

Hi @andrewhaicalis ,

 

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 )
    )

v-henryk-mstf_0-1618478231025.png

 

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.

Hi @v-henryk-mstf 

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

 

 

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 

v-henryk-mstf
Community Support
Community Support

Hi @andrewhaicalis ,

 

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.


Looking forward to your reply.

Best Regards,
Henry


andrewhaicalis
Frequent Visitor

I have re added the picture

Screenshot 2021-04-13 140100.png

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors