Forum Discussion
Find First Positive AFTER first negative
The data in the table shown is an appended table. The first one is Supply (all incoming Purchase orders currently scheduled and when they are scheduled to come in) and other table is demand based on all Work Orders and what are required to build them. In the case below we go negative on line 28 (7/19), but then immediately would go positive again on line 29 (7/20). I'm just not sure how to say look at the line above and IF it was the first negative then tell me when I reach the first positive after that first negative. (Won't lie - only got this far because BI Gorilla -BI Gorilla - YouTube- made a fantastic youtube video on how to do it step by step!)
The table has ALL part numbers sorted by date and then calculated a running total with a function
= (RT_Name as text, MyTable as table, RT_ColumnName as text) =>
let
Source = MyTable,
BuffValues = List.Buffer(Table.Column(MyTable, RT_ColumnName)),
RunningTotal = List.Generate(() => [RT = BuffValues{0}, RowIndex = 0],
each [RowIndex] < List.Count(BuffValues),
each [RT = List.Sum({[RT], BuffValues{[RowIndex] + 1}}),
RowIndex = [RowIndex] + 1],
each [RT]),
Consolidation = Table.FromColumns(
Table.ToColumns(Source) & {Value.ReplaceType(RunningTotal, type {Int64.Type})},
Table.ColumnNames(Source) & {RT_Name})
in
Consolidation
Please provide that sample data in usable form (not as a screenshot)
- DemingPDCA3 years agoHelper II
Does this work - can't seem to get a pbix file to work correctly. Looking for a way to return 7/14/2023 (first positive after first negative) and -2 max negative it went during that time period
Part Number Due Qty Due Date On Hand Part Wise Running Total Updated Running Total Index CheckStatus B -5 12/21/2022 7 -5 2 9089 Positive B -1 6/20/2023 7 -6 1 9090 Positive B -1 6/23/2023 7 -7 0 9091 Zero B -1 6/29/2023 7 -8 -1 9092 Negative B -1 7/5/2023 7 -9 -2 9093 Negative B 7 7/14/2023 7 -2 5 9094 Positive B -1 7/14/2023 7 -3 4 9095 Positive