Forum Discussion
Avoiding Circular Discrepancies for Col A = Col B (last row) and Col B = Col A (current row)?
Here's the information you're looking for.
- In the future, please upload an attachment instead; I spent quite a while copying from it.
- The difficult part is writing the measure of "01_Beginning Inventory". I have provided compatibility for various time dimensions (daily, weekly, monthly, quarterly, semi-annually, and annually) of the measure.
- Note that your "Current Inventory" table should include a date dimension.
- Essentially, there are only inbound and outbound operations. Initial, final, and inventory values are derived from these, so the model should be designed with this logic in mind.
01_Beginning Inventory =
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 IN0 =
CALCULATE ( [02_Receipts], DATE_TABLE0 )
VAR OUT0 =
CALCULATE ( [03_Forecast], DATE_TABLE0 )
RETURN
IF ( [03_Forecast], IN0 - OUT0, BLANK () )
02_Receipts =
SUM ( 'Current Inventory'[Current Inventory] ) + SUM ( 'Receipts'[QTY] ) + 0
02_Receipts_Display =
IF ( [03_Forecast], [02_Receipts], BLANK () )
03_Forecast =
SUM ( Forecast[QTY] )
04_Ending Inventory =
VAR DATE_START0 =
CALCULATE ( FIRSTDATE ( 'Calendar'[dates] ), ALL ( 'Calendar' ) )
VAR DATE_END0 =
LASTDATE ( 'Calendar'[dates] )
VAR DATE_TABLE0 =
DATESBETWEEN ( 'Calendar'[dates], DATE_START0, DATE_END0 )
VAR IN0 =
CALCULATE ( [02_Receipts], DATE_TABLE0 )
VAR OUT0 =
CALCULATE ( [03_Forecast], DATE_TABLE0 )
RETURN
IF ( [03_Forecast], IN0 - OUT0, BLANK () )
this?
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 () )
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] ) ),
"@END0", 'Measure'[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]
VAR ItemID = [Item ID]
VAR T = FILTER ( T4, [Item ID] = ItemID && [@Index] <= R )
VAR X =
SWITCH (
TRUE (),
R >= [@minIndex] && [@Diff] < 0, [@END0] - [@Diff],
R < [@minIndex] && [@Diff] < 0, 0,
[@END0]
)
RETURN
X
)
VAR T6 = FILTER ( T5, [Dates] = DATE_AC && [Item ID] = ItemID_AC )
VAR RESULT = SUMX ( T6, [@END1] )
RETURN
RESULT
- Satya28043 years agoFrequent Visitor
Thanks a ton.. will try it out in my dataset
- Satya28043 years agoFrequent Visitor
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 - jiaopengzi3 years agoFrequent Visitor
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 RESULT01_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?