Forum Discussion
Avoiding Circular Discrepancies for Col A = Col B (last row) and Col B = Col A (current row)?
Hi jiaopengzi .. it was working like a charm but noticed the index restarts everytime the 04 Ending inventory is 0 right? In sample below, for Aug'23, previous ending inventory plus receipts (2642 + 2206) is greater than forecast (4556) so we expect a positive ending inventory display but the measure gives 0. Any magical fixes you would suggest?
| Month | Product | Forecast | Receipts | 04_Ending Inventory | 04_Ending Inventory_Display |
| 01-05-2023 | AB | 5278 | 3009 | -2269 | 0 |
| 01-06-2023 | AB | 4686 | 7202 | 247 | 2515 |
| 01-07-2023 | AB | 3708 | 3835 | 373 | 2642 |
| 01-08-2023 | AB | 4556 | 2206 | -1977 | 0 |
| 01-09-2023 | AB | 4083 | 6177 | 118 | 2094 |
| 01-10-2023 | AB | 1051 | 1169 | 3145 |
Just add some judgment logic, hope to help you.
04_Ending Inventory_Display =
VAR ItemID_AC = SELECTEDVALUE('Items'[Item ID])
VAR DATE_AC = LASTDATE('Calendar'[Dates])
VAR T0 =
FILTER (
ADDCOLUMNS (
CROSSJOIN ( ALL( 'Calendar'[Dates] ), ALL( 'Items'[Item ID] ) ),
"@IN",[02_Receipts_Display],
"@OUT",[03_Forecast],
"@END0", [04_Ending Inventory]
),
NOT ( ISBLANK ( [@END0] ) )
)
VAR T1 =
ADDCOLUMNS (
T0,
"@Index",
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
T0,
ORDERBY ( 'Calendar'[Dates], ASC, Items[Item ID], ASC ),
PARTITIONBY ( Items[Item ID] )
)
)
)
VAR T2 =
ADDCOLUMNS (
T1,
"@Acc",
VAR R = [@Index]
VAR ItemID = [Item ID]
VAR T = FILTER ( T1, [Item ID] = ItemID && [@Index] <= R )
VAR Acc = SUMX ( T, [@END0] )
RETURN
Acc
)
VAR T3 =
ADDCOLUMNS (
T2,
"@isAdd",
VAR R = [@Index]
VAR ItemID = [Item ID]
VAR T = FILTER ( T2, [Item ID] = ItemID && [@Index] <= R )
RETURN
MINX ( T, [@Acc] )
)
VAR T4 =
ADDCOLUMNS (
T3,
"@minIndex",
VAR R = [@Index]
VAR ItemID = [Item ID]
VAR T = FILTER ( T3, [Item ID] = ItemID && [@Index] <= R )
VAR MIN0 = MINX ( T, [@Acc] )
VAR TT = FILTER ( T, [@isAdd] = MIN0 )
VAR I = MINX ( TT, [@Index] )
RETURN
I,
"@Diff",
VAR R = [@Index]
VAR ItemID = [Item ID]
VAR T = FILTER ( T3, [Item ID] = ItemID && [@Index] <= R )
VAR MIN0 = MINX ( T, [@Acc] )
VAR TT = FILTER ( T, [@isAdd] = MIN0 )
VAR I = MINX ( TT, [@Index] )
VAR DIFF = SUMX ( FILTER ( T, [@Index] = I ), [@END0] )
RETURN
DIFF
)
VAR T5 =
ADDCOLUMNS (
T4,
"@END1",
VAR R = [@Index] - 1
VAR ItemID = [Item ID]
VAR T = FILTER ( T4, [Item ID] = ItemID && [@Index] = R )
VAR END0_Previous = SUMX ( T, [@END0] )
VAR X =
SWITCH (
TRUE (),
[@END0] < 0 && [@IN] - [@OUT] > 0, [@IN] - [@OUT],
R >= [@minIndex] && [@Diff] < 0, [@END0] - [@Diff],
R < [@minIndex] && [@Diff] < 0, 0,
END0_Previous < 0, [@END0] - END0_Previous,
[@END0]
)
RETURN
X
)
VAR T6 =
ADDCOLUMNS (
T5,
"@BEGIN1",
VAR R = [@Index] - 1
VAR ItemID = [Item ID]
VAR T = FILTER ( T5, [Item ID] = ItemID && [@Index] = R )
VAR END1_Previous = SUMX ( T, [@END1] )
VAR BEGIN1 = IF ( END1_Previous < 0 || END1_Previous = BLANK(), 0, END1_Previous )
RETURN
BEGIN1
)
VAR T7 =
ADDCOLUMNS (
T6,
"@END2",
SWITCH (
TRUE (),
[@BEGIN1] + [@IN] - [@OUT] > 0, [@BEGIN1] + [@IN] - [@OUT],
[@END0]<0 && [@BEGIN1] + [@IN] - [@OUT] <= 0, 0,
[@END0]
)
)
VAR T8 = FILTER ( T7, [Dates] = DATE_AC && [Item ID] = ItemID_AC )
VAR RESULT = SUMX ( T8, [@END2] )
RETURN
RESULT
01_Beginning Inventory_Display =
VAR DATE_START0 =
CALCULATE ( FIRSTDATE ( 'Calendar'[dates] ), ALL ( 'Calendar' ) )
VAR DATE_END0 =
LASTDATE ( 'Calendar'[dates] )
VAR TF0 =
HASONEVALUE ( 'Calendar'[dates] ) //日
VAR TF1 =
HASONEVALUE ( 'Calendar'[YearWeek] ) //周
VAR TF2 =
HASONEVALUE ( 'Calendar'[YearMonth] ) //月
VAR TF3 =
HASONEVALUE ( 'Calendar'[YearQuarter] ) //季度
VAR TF4 =
HASONEVALUE ( 'Calendar'[YearHalf] ) //半年度
VAR TF5 =
HASONEVALUE ( 'Calendar'[FY00] ) //年度
VAR N0 =
SWITCH (
TRUE (),
TF0, 0,
TF1,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfWeek] ),
LASTDATE ( 'Calendar'[EndOfWeek] ),
DAY
),
TF2,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfMonth] ),
LASTDATE ( 'Calendar'[EndOfMonth] ),
DAY
),
TF3,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfQuarter] ),
LASTDATE ( 'Calendar'[EndOfQuarter] ),
DAY
),
TF4,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfHalfYear] ),
LASTDATE ( 'Calendar'[EndOfHalfYear] ),
DAY
),
TF5,
DATEDIFF (
FIRSTDATE ( 'Calendar'[StartOfYear] ),
LASTDATE ( 'Calendar'[EndOfYear] ),
DAY
),
0
) + 1
VAR DATE_END1 =
SWITCH (
TRUE (),
TF0, DATEADD ( DATE_END0, - N0, DAY ),
TF1, DATEADD ( LASTDATE ( 'Calendar'[EndOfWeek] ), - N0, DAY ),
TF2, DATEADD ( LASTDATE ( 'Calendar'[EndOfMonth] ), - N0, DAY ),
TF3, DATEADD ( LASTDATE ( 'Calendar'[EndOfQuarter] ), - N0, DAY ),
TF4, DATEADD ( LASTDATE ( 'Calendar'[EndOfHalfYear] ), - N0, DAY ),
TF5, DATEADD ( LASTDATE ( 'Calendar'[EndOfYear] ), - N0, DAY ),
DATE_START0 //无筛选的时,默认日期表期初。
)
VAR DATE_END2 =
IF ( ISBLANK ( DATE_END1 ), DATE_START0, DATE_END1 ) //兼容 dateadd 后的空值,注意日期表的两个端点。
VAR DATE_TABLE0 =
DATESBETWEEN ( 'Calendar'[dates], DATE_START0, DATE_END2 )
VAR _Begin =
CALCULATE ( [04_Ending Inventory_Display], DATE_TABLE0 )
VAR IN0 =
CALCULATE ( [02_Receipts], DATE_TABLE0 )
VAR OUT0 =
CALCULATE ( [03_Forecast], DATE_TABLE0 )
RETURN
IF ( [03_Forecast], COALESCE( _Begin,IN0-OUT0), BLANK () )
- Satya28043 years agoFrequent Visitor
Thanks jiaopengzi .. it is one step closer.. but for September it is not carrying forward the August ending on hand. it should be (293+6617)-4803 but it is calculating 6617-4803 = 1814.
Am trying to replicate your logic in excel to understand it better but am struggling to recreate it after Acc.. Do you mind sharing a table as to what are intended values for Index, Acc, isadd, minIndex, Diff, End1 you are using in your measure logic for my sample data?
- jiaopengzi3 years agoFrequent Visitor
It's me who overcomplicated your question. The conventional invoicing idea should not be used, and the conventional invoicing should not have negative numbers.
Then change the way of thinking and solve the problem.01_Beginning Inventory_Display = VAR ItemID_AC = SELECTEDVALUE('Items'[Item ID]) VAR DATE_AC = LASTDATE('Calendar'[Dates]) VAR T0 = FILTER ( ADDCOLUMNS ( CROSSJOIN ( ALL( 'Calendar'[Dates] ), ALL( 'Items'[Item ID] ) ), "@IN",[02_Receipts_Display], "@OUT",[03_Forecast], "@END0", [02_Receipts_Display] - [03_Forecast] ), NOT ( ISBLANK ( [@END0] ) ) ) VAR T1 = ADDCOLUMNS ( T0, "@Index", COUNTROWS ( WINDOW ( 1, ABS, 0, REL, T0, ORDERBY ( 'Calendar'[Dates], ASC, Items[Item ID], ASC ), PARTITIONBY ( Items[Item ID] ) ) ) ) VAR T2 = ADDCOLUMNS ( T1, "@Acc", VAR R = [@Index] VAR ItemID = [Item ID] VAR T = FILTER ( T1, [Item ID] = ItemID && [@Index] <= R ) VAR Acc = SUMX ( T, [@END0] ) RETURN Acc ) VAR T3 = ADDCOLUMNS ( T2, "@END1", VAR R = [@Index] VAR ItemID = [Item ID] VAR T = FILTER ( T2, [Item ID] = ItemID && [@Index] <= R ) VAR minOfSum = MIN ( 0, MINX ( T, [@acc] ) ) RETURN [@acc] - minOfSum ) VAR T4 = ADDCOLUMNS ( T3, "@BEGIN1", VAR R = [@Index] VAR ItemID = [Item ID] VAR T = FILTER ( T3, [Item ID] = ItemID && [@Index] = R - 1) VAR END1_Previous = SUMX ( T, [@END1] ) VAR BEGIN1 = IF ( END1_Previous < 0 || END1_Previous = BLANK(), 0, END1_Previous ) RETURN BEGIN1 ) VAR T5 = ADDCOLUMNS ( T4, "@END2", IF([@BEGIN1] + [@IN] - [@OUT] > 0, [@BEGIN1] + [@IN] - [@OUT],0) ) VAR T6 = FILTER ( T5, [Dates] = DATE_AC && [Item ID] = ItemID_AC ) VAR RESULT = SUMX ( T6, [@BEGIN1] ) RETURN RESULT04_Ending Inventory_Display = VAR ItemID_AC = SELECTEDVALUE('Items'[Item ID]) VAR DATE_AC = LASTDATE('Calendar'[Dates]) VAR T0 = FILTER ( ADDCOLUMNS ( CROSSJOIN ( ALL( 'Calendar'[Dates] ), ALL( 'Items'[Item ID] ) ), "@IN",[02_Receipts_Display], "@OUT",[03_Forecast], "@END0", [02_Receipts_Display] - [03_Forecast] ), NOT ( ISBLANK ( [@END0] ) ) ) VAR T1 = ADDCOLUMNS ( T0, "@Index", COUNTROWS ( WINDOW ( 1, ABS, 0, REL, T0, ORDERBY ( 'Calendar'[Dates], ASC, Items[Item ID], ASC ), PARTITIONBY ( Items[Item ID] ) ) ) ) VAR T2 = ADDCOLUMNS ( T1, "@Acc", VAR R = [@Index] VAR ItemID = [Item ID] VAR T = FILTER ( T1, [Item ID] = ItemID && [@Index] <= R ) VAR Acc = SUMX ( T, [@END0] ) RETURN Acc ) VAR T3 = ADDCOLUMNS ( T2, "@END1", VAR R = [@Index] VAR ItemID = [Item ID] VAR T = FILTER ( T2, [Item ID] = ItemID && [@Index] <= R ) VAR minOfSum = MIN ( 0, MINX ( T, [@acc] ) ) RETURN [@acc] - minOfSum ) VAR T4 = ADDCOLUMNS ( T3, "@BEGIN1", VAR R = [@Index] VAR ItemID = [Item ID] VAR T = FILTER ( T3, [Item ID] = ItemID && [@Index] = R - 1) VAR END1_Previous = SUMX ( T, [@END1] ) VAR BEGIN1 = IF ( END1_Previous < 0 || END1_Previous = BLANK(), 0, END1_Previous ) RETURN BEGIN1 ) VAR T5 = ADDCOLUMNS ( T4, "@END2", IF([@BEGIN1] + [@IN] - [@OUT] > 0, [@BEGIN1] + [@IN] - [@OUT],0) ) VAR T6 = FILTER ( T5, [Dates] = DATE_AC && [Item ID] = ItemID_AC ) VAR RESULT = SUMX ( T6, [@END2] ) RETURN RESULT