topic Re: Calculation inventory values in DAX Commands and Tips

Calculation inventory values

lreinhard77 — Mon, 24 Apr 2023 19:30:08 GMT

Hi,

I want to calculate inventory values as exemplified in the following screenprint.
My problem is related to the issue price calculation for which I need the **bleep** value and **bleep** qty from the previous row.
I already tried many things but always get a circular dependency message.
Does anyone has a tip how to fix this issue and can help me with the correct formula for the issue price calculation?

Re: Calculation inventory values

Anonymous — Wed, 26 Apr 2023 01:54:59 GMT

Hi lreinhard77 ,

I have created a simple sample, please refer to my pbix file.

Create a measure.

Measure = CALCULATE(SUM('Table'[qTY]),FILTER(ALL('Table'),'Table'[date]<=SELECTEDVALUE('Table'[date])&&'Table'[Item]=SELECTEDVALUE('Table'[Item])))

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

Re: Calculation inventory values

lreinhard77 — Wed, 26 Apr 2023 05:27:03 GMT

Hello,

I tried to make things more clear and prepared some data in Excel.
In short:
I get some data from an accounting system (yellow colored section).
For those data, I want to calculate (a) the issue price because I need it to ultimately calculate (b) the inventory value.
The inventory value is illustrated in the last column.

My problem is that the sales price and sales value (qty * sales price) influences the subsequent sales price calculations.
As an example, the sales price in line 4 is influenced by the sales price (times the sales quantity) in line 2
Unfortunately, I don't know the correct way how to make this calculation in PowerBI.

I already tried many things but always get a circularity error 😞

Re: Calculation inventory values

Anonymous — Wed, 26 Apr 2023 06:10:54 GMT

Hi lreinhard77 ,

Sorry, I'm not smart. Are you trying to calculate the sales pricez column? If according to you 427.5=(0+17100)/40, which column does 40 represent?Cumcurrenh or CurrentBuy?Why is the third column 0?(0-2137.5)/40=53.4375 and how do you get the 55 in (0+17100-2137.5+0+0+0+8100)/55? And why does this result appear in the fourth and fifth row? What is the logic of the calculation?

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

Re: Calculation inventory values

lreinhard77 — Wed, 26 Apr 2023 06:26:34 GMT

Hi,

I tried to make things more clear with the following illustrations.

I am not sure if I can attach you my Excel file here.
Hope those screenprints make things clear.