如何计算运行余额（总计）Power BI

huangapple
go评论98阅读模式
英文:

How to calculate a running balance (totals) Power BI

问题

我之前发过类似的帖子，但没有得到有效的解决方案。

我正在尝试获取一个预测的运行库存总数。我有三个维度：[当前库存]、[使用量]、[购买量]。

第1个月的计算是 ([当前库存] + [第1个月购买量]) - [第1个月使用量]。

然后，下个月的计算将使用上个月的输出，然后计算当前月的购买量 - 当前月的使用量。

这是计算的重要部分，也是我遇到问题的地方：如果某个月没有数据（使用量和/或购买量），那么使用量或购买量应被视为0。因此，库存水平将滚动到下个月。

以下是示例数据和示例输出：

当前库存：

  1. 物料	库存数量
  3. A    	100
  5. B    	500
  7. C    	1000

购买量：

  1. 物料	购买日期	数量
  3. A    	01/01/2023	10
  5. A    	01/02/2023	20
  7. A    	01/04/2023	30
  9. B    	01/01/2023	10
  11. B    	01/02/2023	20
  13. B    	01/04/2023	15

使用量：

  1. 物料	使用日期	数量
  3. A    	01/01/2023	50
  5. A    	01/02/2023	10
  7. A    	01/04/2023	20
  9. B    	01/01/2023	10
  11. B    	01/02/2023	30
  13. B    	01/04/2023	40

期望的输出：

  1. | 物料  |01/01/2023 |  01/02/2023 |  01/03/2023 |  01/04/2023 |
  3. | A     | 60        |70          |70          |80          |
  5. | B     | 410       |420         |420         |395         |

希望这可以帮助。

英文:

I posted something similar before but didn't get a solution that worked.

I am trying to get a predicted running stock total. I have 3 dimensions [Current Stock], [Usage], [Purchases]

The calculation for month 1 is ([Current Stock] + [purchases (for month 1]) - [usage (for month 1)]

Then the following month the calculation will use the previous months output and then calculate the current months purchases - the current month usage.

This is the important bit of the calculation and where I am having trouble: If there is no data for a particular month (usage & or purchases) then usage or purchases should be classed as 0. So the stock levels would roll over to the next month.

Example data and example outputs are below:

Current Stock;

  1. Material	Stock on Hand
  3. A          100
  5. B          500
  7. C          1000

Purchases

  1. Material	PurchDate	Quantity
  3. A    	01/01/2023	10
  5. A    	01/02/2023	20
  7. A    	01/04/2023	30
  9. B    	01/01/2023	10
  11. B    	01/02/2023	20
  13. B    	01/04/2023	15

Usage

  1. Material	UseageDate	 Quantity
  3. A       01/01/2023	    50
  5. A   	01/02/2023  	10
  7. A   	01/04/2023	    20
  9. B    	01/01/2023	    10
  11. B    	01/02/2023   	30
  12.  
  13. B    	01/04/2023	    40

Desired output

  1. | Material  |01/01/2023 |  01/02/2023 |  01/03/2023 |  01/04/2023 |
  3. | A         | 60        |70         |70         |80         |
  6. | B         | 410	    |420        |420        |395        |

(Stock on hand + Purch) - Usage(Previous month LoB + Purchases) - usage

hope you can help

答案1

得分: 0

在Power Query (M) 中尝试这个：

  1. let 
  2.     StartDate = List.Min(Purchases[PurchDate] & Usage[UseageDate]),
  3.     EndDate = List.Max(Purchases[PurchDate] & Usage[UseageDate]),
  4.     dates = List.Distinct(List.Transform({Number.From(StartDate)..Number.From(EndDate)}, each Date.StartOfMonth(Date.From(_)))),
  5.     #"Added Custom" = Table.AddColumn(Table.FromList(List.Distinct(Stock[Column1] & Purchases[Material] & Usage[Material]), null, {"Material"}), "Custom", each dates),
  6.     #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
  7.     #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Stock", (x) => List.Sum(Table.SelectRows(Stock, each x[Custom] = StartDate and [Column1] = x[Material])[Column2])??0),
  8.     #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Purchases", (x) => List.Sum(Table.SelectRows(Purchases, each [Material] = x[Material] and [PurchDate] = x[Custom])[Quantity])??0),
  9.     #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Usage", (x) => List.Sum(Table.SelectRows(Usage, each [Material] = x[Material] and [UseageDate] = x[Custom])[Quantity])??0),
  10.     #"Grouped Rows" = Table.Group(#"Added Custom3", {"Material"}, {{"data", each 
  11.         let #"Added Index" = Table.AddIndexColumn(Table.Sort(_, {{"Custom", Order.Ascending}}), "Index", 0, 1, Int64.Type),
  12.             #"Added Cum Total" = Table.AddColumn(#"Added Index", "StockCum", each List.Sum(List.FirstN(#"Added Index"[Stock], [Index] + 1))),
  13.             #"Added Cum Total2" = Table.AddColumn(#"Added Cum Total", "UsageCum", each List.Sum(List.FirstN(#"Added Index"[Usage], [Index] + 1))),
  14.             #"Added Cum Total3" = Table.AddColumn(#"Added Cum Total2", "PurchasesCum", each List.Sum(List.FirstN(#"Added Index"[Purchases], [Index] + 1))),
  15.             #"AddBalance" = Table.AddColumn(#"Added Cum Total3", "EndingBalance", each [StockCum] - [UsageCum] + [PurchasesCum], type number)
  16.         in #"AddBalance"
  17.     , type table [Custom=nullable date, EndingBalance=number] }}),
  18.     #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Custom", "EndingBalance"}, {"Date", "EndingBalance"}),
  19.     #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Date", type text}}, "en-US")[Date]), "Date", "EndingBalance", List.Sum)
  20. in #"Pivoted Column"

这是您提供的Power Query (M) 代码的翻译部分。如果需要进一步的帮助，请随时告诉我。

英文:

Try this in powerquery (m)

  1. let StartDate=List.Min(Purchases[PurchDate]&Usage[UseageDate]),
  2. EndDate=List.Max(Purchases[PurchDate]&Usage[UseageDate]),
  3. dates=List.Distinct(List.Transform({Number.From(StartDate)..Number.From(EndDate)}, each Date.StartOfMonth(Date.From(_)))),
  4. #"Added Custom" = Table.AddColumn(Table.FromList(List.Distinct(Stock[Column1] & Purchases[Material] &Usage[Material]),null, {"Material"}), "Custom", each dates),
  5. #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
  6. #"Added Custom1" = Table.AddColumn(#"Expanded Custom","Stock",(x)=>List.Sum(Table.SelectRows(Stock, each x[Custom]=StartDate and [Column1]=x[Material])[Column2])??0),
  7. #"Added Custom2" = Table.AddColumn(#"Added Custom1","Purchases",(x)=>List.Sum(Table.SelectRows(Purchases, each [Material]=x[Material] and [PurchDate]=x[Custom])[Quantity])??0),
  8. #"Added Custom3" = Table.AddColumn(#"Added Custom2","Usage",(x)=>List.Sum(Table.SelectRows(Usage, each [Material]=x[Material] and [UseageDate]=x[Custom])[Quantity])??0),
  9. #"Grouped Rows" = Table.Group(#"Added Custom3", {"Material"}, {{"data", each 
  10.     let #"Added Index" = Table.AddIndexColumn(Table.Sort(_,{{"Custom", Order.Ascending}}), "Index", 0, 1, Int64.Type),
  11.     #"Added Cum Total" = Table.AddColumn(#"Added Index", "StockCum", each List.Sum(List.FirstN(#"Added Index"[Stock],[Index]+1))),
  12.     #"Added Cum Total2" = Table.AddColumn(#"Added Cum Total", "UsageCum", each List.Sum(List.FirstN(#"Added Index"[Usage],[Index]+1))),
  13.     #"Added Cum Total3" = Table.AddColumn(#"Added Cum Total2", "PurchasesCum", each List.Sum(List.FirstN(#"Added Index"[Purchases],[Index]+1))),
  14.     #"AddBalance" = Table.AddColumn(#"Added Cum Total3","EndingBalance", each [StockCum]-[UsageCum]+[PurchasesCum], type number)
  15.     in #"AddBalance"
  16. , type table [Custom=nullable date, EndingBalance=number] }}),
  17. #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Custom", "EndingBalance"}, {"Date", "EndingBalance"}),
  18. #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Date", type text}}, "en-US")[Date]), "Date", "EndingBalance", List.Sum)
  19. in  #"Pivoted Column"

如何计算运行余额（总计）Power BI

huangapple
  • 本文由 发表于 2023年6月19日 21:37:11
  • 转载请务必保留本文链接：https://go.coder-hub.com/76507185.html
  • dax
  • powerbi
  • powerbi-desktop
  • powerquery
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定