Как рассчитать текущий баланс (итоги) Power BI
Я публиковал что-то подобное раньше, но не получил решения, которое сработало.
Я пытаюсь получить прогнозируемую общую сумму запасов. У меня есть 3 измерения [Текущий запас], [Использование], [Покупки]
Расчет за 1 месяц ([Current Stock] + [purchases (for month 1]) - [usage (for month 1)]
Затем в следующем месяце при расчете будут использоваться выходные данные за предыдущие месяцы, а затем будут рассчитаны покупки за текущий месяц - использование за текущий месяц.
Это важная часть расчета, и здесь у меня возникают проблемы: если нет данных за конкретный месяц (использование и или покупки), то использование или покупки должны быть классифицированы как 0. Таким образом, уровни запасов будут переноситься на следующий месяц. месяц.
Примеры данных и примеры выходных данных приведены ниже:
текущий запас;
Material Stock on Hand
A 100
B 500
C 1000
Покупки
Material PurchDate Quantity
A 01/01/2023 10
A 01/02/2023 20
A 01/04/2023 30
B 01/01/2023 10
B 01/02/2023 20
B 01/04/2023 15
Применение
Material UseageDate Quantity
A 01/01/2023 50
A 01/02/2023 10
A 01/04/2023 20
B 01/01/2023 10
B 01/02/2023 30
B 01/04/2023 40
Желаемый результат
| Material |01/01/2023 | 01/02/2023 | 01/03/2023 | 01/04/2023 |
| A | 60 |70 |70 |80 |
| B | 410 |420 |420 |395 |
(Stock on hand + Purch) - Usage(Previous month LoB + Purchases) - usage
надеюсь, вы можете помочь
Ответы 1
Попробуйте это в powerquery (m)
let StartDate=List.Min(Purchases[PurchDate]&Usage[UseageDate]),
EndDate=List.Max(Purchases[PurchDate]&Usage[UseageDate]),
dates=List.Distinct(List.Transform({Number.From(StartDate)..Number.From(EndDate)}, each Date.StartOfMonth(Date.From(_)))),
#"Added Custom" = Table.AddColumn(Table.FromList(List.Distinct(Stock[Column1] & Purchases[Material] &Usage[Material]),null, {"Material"}), "Custom", each dates),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom","Stock",(x)=>List.Sum(Table.SelectRows(Stock, each x[Custom]=StartDate and [Column1]=x[Material])[Column2])??0),
#"Added Custom2" = Table.AddColumn(#"Added Custom1","Purchases",(x)=>List.Sum(Table.SelectRows(Purchases, each [Material]=x[Material] and [PurchDate]=x[Custom])[Quantity])??0),
#"Added Custom3" = Table.AddColumn(#"Added Custom2","Usage",(x)=>List.Sum(Table.SelectRows(Usage, each [Material]=x[Material] and [UseageDate]=x[Custom])[Quantity])??0),
#"Grouped Rows" = Table.Group(#"Added Custom3", {"Material"}, {{"data", each
let #"Added Index" = Table.AddIndexColumn(Table.Sort(_,{{"Custom", Order.Ascending}}), "Index", 0, 1, Int64.Type),
#"Added Cum Total" = Table.AddColumn(#"Added Index", "StockCum", each List.Sum(List.FirstN(#"Added Index"[Stock],[Index]+1))),
#"Added Cum Total2" = Table.AddColumn(#"Added Cum Total", "UsageCum", each List.Sum(List.FirstN(#"Added Index"[Usage],[Index]+1))),
#"Added Cum Total3" = Table.AddColumn(#"Added Cum Total2", "PurchasesCum", each List.Sum(List.FirstN(#"Added Index"[Purchases],[Index]+1))),
#"AddBalance" = Table.AddColumn(#"Added Cum Total3","EndingBalance", each [StockCum]-[UsageCum]+[PurchasesCum], type number)
in #"AddBalance"
, type table [Custom=nullable date, EndingBalance=number] }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Custom", "EndingBalance"}, {"Date", "EndingBalance"}),
#"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)
in #"Pivoted Column"
Привет, спасибо, что ответили на это. Это решение работает! (с некоторыми изменениями с группировкой по нескольким строкам использования/покупки для одних и тех же дат) Однако есть ли способ динамически выбирать диапазон дат на основе самой большой и самой маленькой даты использования/покупки? Прямо сейчас вы по умолчанию используете #StartDate и #EndDate. Большое спасибо, Джош.
Удалена логическая бомба для повторяющихся дат. Теперь также автоматически вытягивает дату начала/окончания. Если это сработает для вас, примите решение как решенное и, возможно, проголосуйте. Спасибо
Спасибо, большая помощь
Где твой предыдущий пост и где ты застрял? Вы ищете DAX или Power Query? Horsey Ride предоставила решение с помощью Power Query.