Calcular stock remanente

[Crisvilla88]

Estimados, en base a la imagen adjunta requiero lo siguiente:

- A través de DAX, agregar una columna adicional que vaya mostrando el stock remanente por cada producto.

- Como ven, la tabla tiene varios productos. Cada producto tiene como primera linea el Stock Inicial. A partir de este stock inicial, necesito ir restando la demanda y sumando las entradas de inventario que tenga.

Por ejemplo:

El producto 1005304 tiene stock inicial de 16.620 unidades. Necesito que el stock remanente de Enero sea el stock inicial (16.620) - Demanda Enero (4.984) = 11.636. 

Luego el stock de Febrero sea el Stock de cierre del mes anterior (11.636) - Demanda Febrero (1.650) = 9.986.

Además debe incorporar como Stock, las entradas de inventario de los meses futuros.

Esto quiere decir, que considerando el mismo producto y la imagen arriba, el stock de cierre de Marzo sera = Stock Cierre Febrero + Entrada Stock Marzo - Demanda de Marzo.

Agradezco vuestro apoyo.

[Crisvilla88]

Dear @Anonymous , Thanks for your response.

It looks similar, but is not the same.

Here I share with you what I need. Considering the image below, I need the "Final Stock" Column in PBI.

as you can note, the forst row of each product is the Initial Stock. Then, the "Final Stock" is adding the supply of new stock minus the demand.

Hope this clarify.

Thanks in advance.

[SachinNandanwar]

Why didnt you conside the value of 750 in the calculation from the month of March ?

[Crisvilla88]

Hi @SachinNandanwar I see that is considered.

The "Final Stock" Of March is = Final Stock of February (9.986) - Demand of March (5.700) + Supply (750) = 5.036.

[SachinNandanwar]

Oh. Thats 0.750 and not 750

[Crisvilla88]

All the data its on thousand units 😉

[SachinNandanwar]

You need to index your records to uniquely identify each rows. If you look at the first three rows you have dates 29-02-2024, 30-03-2024 followed by another 29-02-2024.

DAX will consider the order of the two 29-02-2024 records first and then it will consider 30-03-2024 in its calculation.

Take a look at this sample data and its output >>>

Code,Date,Year,Demand,Supply,SelectionOn
1,29-02-2024,2024,0,16620,M01/2024
1,30-01-2024,2024,4984,0,M02/2024
1,29-02-2024,2024,1650,0,M03/2024
1,30-03-2024,2024,5700,750,M04/2024
1,30-04-2024,2024,4950,8875,M05/2024

FinalStock = CALCULATE(SUM('Table'[Supply])-SUM('Table'[Demand]),ALLEXCEPT('Table','Table'[Date]),'Table'[Date]<=MAX('Table'[Date]))

Have a look at the output after adding an index column.

Sample Data >>
Index.Code,Date,Year,Demand,Supply,SelectionOn
1,1,29-02-2024,2024,0,16620,M01/2024
2,1,30-01-2024,2024,4984,0,M02/2024
3,1,29-02-2024,2024,1650,0,M03/2024
4,1,30-03-2024,2024,5700,750,M04/2024
5,1,30-04-2024,2024,4950,8875,M05/2024

FinalStock = CALCULATE(SUM('Table'[Supply])-SUM('Table'[Demand]),ALLEXCEPT('Table','Table'[Index]),'Table'[Index]<=MAX('Table'[Index]))

I guess its similar to what you want.

[Crisvilla88]

Thanks @SachinNandanwar . 

How do I handle the indexes?. should be defined by product?.

[SachinNandanwar]

Hi @Crisvilla88 ,

You would have to create them probably based on material/product column ensuring that perdiod/section that has a value "Stock" always is assigned with value 1 and the rest of the records are incremented by 1 based on the order of the month and year.

[Crisvilla88]

Dear,

I try but it seems that something its wrong

[SachinNandanwar]

Please share some sample data or the PBI file.

[Crisvilla88]

Hope this works, because I don't have access to share the PBI file here

https://drive.google.com/file/d/1KJ3dsKpW5lR06NIStx2LVwnYC_ouZ28E/view?usp=drive_link

[SachinNandanwar]

It says access denied.

[Crisvilla88]

Sorry. Could you try again please.

https://drive.google.com/file/d/1KJ3dsKpW5lR06NIStx2LVwnYC_ouZ28E/view?usp=drive_link

[Crisvilla88]

dear @SachinNandanwar ,

I think that I got it adding the "code" on the ALLEXCEPT function

[SachinNandanwar]

Hi @Crisvilla88 ,

I was just having a look at your file.You can replace ALLEXCEPT with ALLSELECTED. Your intial requirement didnt mention that you would filter on Codes

F.stock = CALCULATE(SUM('Proyección'[Supply])-SUM('Proyección'[Demand]),ALLSELECTED('Proyección'[Index]),'Proyección'[Index]<=MAX('Proyección'[Index]))

[Crisvilla88]

Yes!, I would like to filter codes, and then add family, and subfamily then.

I try with ALLSELECTED, but it does not work. It works adding 'Proyección'[Code] on the ALLEXCEPT function.

I do this because I want to estimate the final stock of each month and see it in a graph.

The issue that I see, is that the PBI shows that the final stock of Mach is 8.966.609, but in reality is 7.225.012.

I have downloaded the table of the PBI in a CSV file, and if you sum the Final stock, you can not the difference.

PBI: 

https://drive.google.com/file/d/1rQdUxAbG5Q1mJ8AmvrQrHVUYSpWl93JL/view?usp=sharing

CSV: https://drive.google.com/file/d/1rQdUxAbG5Q1mJ8AmvrQrHVUYSpWl93JL/view?usp=drive_link

[Anonymous]

Hi @Crisvilla88 ,

I create a table as you mentioned.

Then I create a calculated column.

Column = 
200
    - CALCULATE (
        SUM ( 'Table'[Total] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Number] = EARLIER ( 'Table'[Number] )
                && 'Table'[Name] <= EARLIER ( 'Table'[Name] )
        )
    )

Best Regards

Yilong Zhou

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