PROJECTION of an stock in POWERBI

[santigc97]

Hello,

I have a tool that provides me with this data (actual stock and inflow and outflow for a specific date and location) and I usually create a report in Excel using a simple formula.

It is called projection, for the first date is equal to the Stock OH and for the next dates it is the previous projection + Inflow – Outflow and this to the infinite…

Product	*LOC	OH	Type	23/04/2024	29/04/2024	06/05/2024	13/05/2024
A	SPAIN	20	OUTFLOW	5	15	20	5
A	SPAIN	20	INFLOW	0	100	0	0
A	SPAIN	20	PROJECTION	20	15	100	80
A	GERMANY	10	OUTFLOW	10	10	10	10
A	GERMANY	10	INFLOW	0	0	0	0
A	GERMANY	10	PROJECTION	10	0	-10	-20
				=OH	=PROJ(0)+OUTFLOW(0)+INFLOW(0)	=PROJ(1)+OUTFLOW(1)+INFLOW(1)	=PROJ(2)+OUTFLOW(2)+INFLOW(2)

Now I have access to the database and I am able to download the information that is similar to the following table, as it has the data I tried to create a new column that is cero by default for modify it later, them an unpivot and outside the data model formular but I always have circular references. How can I manipulate the data to have the columns with an unpivot for introducing them in a matrix?

Date	ITEM	*LOC	OUTFLOW	INFLOW	OH (First OH)
23/04/2024	A	SPAIN	5	0	20
29/04/2024	A	SPAIN	15	100	20
06/05/2024	A	SPAIN	20	0	20
13/05/2024	A	SPAIN	5	0	20
23/04/2024	A	GERMANY	10	0	10
29/04/2024	A	GERMANY	10	0	10
06/05/2024	A	GERMANY	10	0	10
13/05/2024	A	GERMANY	10	0	10

[v-yangliu-msft]

Hi  @santigc97 ,

Here are the steps you can follow：

1. Select [OUTFLOW], [INFLOW],[OH] – Transform – Unpiovt Column.

2. Placement of fields in the matrix.

Row headers -- +/-icons set off – Stepped layout set off.

3. Result:

It's not clear how the blank values below are calculated, since 2024.4.23 doesn't show any data on Germany.

Best Regards,

Liu Yang

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

[santigc97]

Hello,

The problem is that in your solution we don't have the projection part which is the most important, only the OH will be om in the 23/04. In the other is the calculation

=PROJ(0)+OUTFLOW(0)+INFLOW(0)
On which the data is the one of the last day