库存预测模型 [英] Inventory Forecasting model

问题描述

大家好，

论坛的新内容。

我有一个关于powerpivot / DAX的问题我一直无法解决。我希望有人能够应对这一挑战......
b
$
如果没有库存可以满足预测，问题在于计算YTD月度库存结算仓位而忽略累积负数。

有点难以解释，但附件显示了我想要的结果。





我使用以下商品：
InvMovement：= [购买] - [销售额]

开立INV：=计算（[InvMovement]，FILTER（全部（'DATE'[日期]） ），'DATE'[DATE]< MAX（'DATE'[DATE]）））+ [CURRINV]

ENDING INV：= calculate（[InvMovement]，FILTER（ALL（'DATE'） [DATE]），'DATE'[DATE]< = MAX（'DATE'[DATE]）））+ [CURRINV]

问题在于在某些情况下，我的销售团队预测未来期间的可用库存将超过可用库存。 

当我们缺货时，我们仍然希望保留预测，即使我们无法完成销售。

上面的OPENING INV和ENDING INV计算在库存总是正数时起作用，但在库存不足时累积负数并导致不正确（负）开仓库存位置。

如果ENDING INV结果计算为负数并且下一个周期的OPENING INV也为零，则Id为0。 / span>

Hello all,
new to the forum.
I have an issue with powerpivot/DAX that I have not been able to solve. I hope someone is up for the challenge...

The problem is calculating a YTD monthly Inventory closing position while ignoring cumulative negatives if there is no stock to satisfy the forecast.
Kind of hard to explain but the attached file shows my desired outcome.


Im using the following
InvMovement:=[PURCHASES]-[SALES]
OPENING INV:=calculate([InvMovement],FILTER(ALL(‘DATE'[DATE]),’DATE'[DATE]< MAX(‘DATE'[DATE])))+[CURRINV]
ENDING INV:=calculate([InvMovement],FILTER(ALL(‘DATE'[DATE]),’DATE'[DATE]<= MAX(‘DATE'[DATE])))+[CURRINV]
the problem is that there cases where my Sales Team is forecasting above the available inventory in a future period. 
When we are out of Stock we still want the forecast to remain, even though we cant fill the sale.
The above OPENING INV and ENDING INV calculations work when inventory is always positive, but accumulates the negatives on Out of Stocks and results in incorrect (negative) opening inventory positions.
Id like the ENDING INV results to be 0 if it calculates as negative and OPENING INV of the following period to also be zero.

将此链接粘贴到文件

https://1drv.ms/x/s!AgsUyh0i7varnjMfLmnZ1bPgSSGp

https://1drv.ms/x/s!AgsUyh0i7varnjMfLmnZ1bPgSSGp

推荐答案

带有PowerPivot和Power Query的Excel 365 Pro Plus（又名Get& Transform）

使用PQ将数据转换为PP友好格式。

当值变为负值时重新开始累积。

http://www.mediafire.com/file/ad885oa94rjdbod/03_23_19.xlsx/file

http://www.mediafire.com/file/inl7mad1pjyg7rd/03_23_19.pdf/file

这篇关于库存预测模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！