Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JuanVR10
Frequent Visitor

Sumar valores de una columna hasta que se complete cantidad de stock

Hola Comunidad.

He intentado de muchas formas tratar de solucionar un problema y hasta ahora no he tenido exito. Espero me puedan ayudar.

 

Tengo la siguiente tabla en PBI.  En donde tengo los consumos de stock para un Cliente/Material determinado.

El stock disponible está en la columna "Stock".

Lo que necesito que para el ClienteMat "x" se sume la CantidadEntregada, solo hasta que se cumpla el total del Stock para el mismo ClienteMat (en el ejemplo "30"). La suma la debe realizar considerando la FechaEntrega mas reciente hacia abajo.

 

Captura de pantalla 2022-07-14 002332.png

 

En el ejemplo de la imagen necesito que se identifiquen solo las 5 primeras filas (6*5 = 30).

 

Espero me puedan ayudar.

 

Gracias!!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@JuanVR10 , For On Hand we create measures like

 

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

 

refer if needed

https://radacad.com/calculating-stock-on-hand-using-dax-power-bi-inventory-model

https://community.powerbi.com/t5/Desktop/Calculate-On-Hand-quantity/m-p/183000

View solution in original post

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

Hi @JuanVR10 ,

 

Can you give an example of the relationship between the quantity delivered and the number of days in stock and stock, depending on the delivery date?

vhenrykmstf_0-1658304641054.png

 

 Looking forward to your reply.


Best Regards,
Henry

 

amitchandak
Super User
Super User

@JuanVR10 , For On Hand we create measures like

 

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

 

refer if needed

https://radacad.com/calculating-stock-on-hand-using-dax-power-bi-inventory-model

https://community.powerbi.com/t5/Desktop/Calculate-On-Hand-quantity/m-p/183000

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.