Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Funk-E-Guy
Helper I
Helper I

Avoiding Circular Discrepancies for Col A = Col B (last row) and Col B = Col A (current row)?

My goal: To use DAX to calculate Ending Inventory as a function of MAX(0, Beginning Inventory + Receipts - Forecast), for every day and every SKU.

 

My Issue: I do not know how to lookup the previous day's Ending Inventory without PowerBI declaring a Circular Discrepancy.

 

Clarification: Because Ending Inventory rounds up to 0 at the end of each day if the formula is negative, I cannot collapse my inventory calculation into a single column and subtract all forecast up to a given date and add all receipts up to a given date like this:

 

(PseudoCode)
Invalid Projected Inventory =
Max(
    0,
    LOOKUPVALUE(Current Inventory)
        + CALCULATE(SUM(Receipts.QTY), Receipts.Date <= Date, Receipts.Item = Item)
        - CALCULATE(SUM(Forecast.QTY), Forecast.Date <= Date, Forecast.Item = Item)
)

 

The reason I cannot use that single Projected Inventory column is to consider the following example:

  • Inventory for today (Oct 14) is 100
  • Total sum of Forecast thru Oct 20 is 200
  • Receipt of 100 will arrive Oct 21
  • Forecast on Oct 21, 2021 is 40

Desired Output: Projected Inventory on Oct 21 is 60

  • Inventory at start of Oct 21 is 0 (100 current inventory - 200 forecast, rounded up to 0)
  • Receipt of 100 on Oct 21 - Forecast of 40 on Oct 21 = Projected Inventory of 60

Output of Invalid Formula: Projected Inventory = 0

  • Current Inventory of 100 - forecast thru Oct 21 of 240 + receipts of 100 = -40
  • Projected Inventory = Max(0, -40) = 0

 

Here is a screenshot of all the data I'm using in my test model (text data at the bottom), including my desired input which I can get working in Excel.

 

PowerBI Rolling Daily Inventory.png

 

And here is the same setup in PowerBI:

PowerBI Rolling Daily Inventory in PBI.png Daily Inventory Relationships.png

 

But when I try to use DAX to calculate the Beginning and Ending inventory of each day, I get a circular discrepancy. Is there any way around this within DAX?

 

Beginning Inventory.png

 

Here are my formulas for the calculated table:

 

Table Source:

 

Daily Inventory = GENERATe(distinct('Date Table'[Date]),  DISTINCT(Items[Item ID]))

 

Beginning Inventory:

 

Beginning Inventory = 
if('Daily Inventory'[Date]=today(), 
    calculate(sum('Current Inventory'[Current Inventory]),filter('Current Inventory', 'Current Inventory'[Item ID]=[Item ID])),
    LOOKUPVALUE('Daily Inventory'[Ending Inventory],'Daily Inventory'[Item ID], [Item ID], 'Daily Inventory'[Date], [Date]-1)
)

 

Receipts:

 

Receipts = CALCULATE(sum(Receipts[QTY]), filter(Receipts, Receipts[Date]=earlier([Date])), filter(Receipts,Receipts[Item ID]=earlier([Item ID])))

 

Forecast:

 

Forecast = CALCULATE(sum(Forecast[QTY]), filter(Forecast, Forecast[Date]=earlier([Date])), filter(Forecast,Forecast[Item ID]=earlier([Item ID])))

 

Ending Inventory:

 

Ending Inventory = max([Beginning Inventory] + [Receipts] - [Forecast],0)

 

 

And here are my data tables:

Date Table

Date
10/14/2021
10/15/2021
10/16/2021
10/17/2021
10/18/2021

Items

Item IDItem Name
1TestSKU1
2TestSKU2

Current Inventory

Item IDCurrent Inventory
1100
2100

Forecast

Item IDDateQTY
110/14/202114
110/15/202112
110/16/20213
110/17/202124
110/18/202116
210/14/202143
210/15/202146
210/16/20217
210/17/202122
210/18/202132

Receipts

Item IDDateQTY
110/17/2021200
210/17/2021200
15 REPLIES 15
jiaopengzi
Frequent Visitor

See if the picture below is the similar result you want, realize the end of the previous period, and put it at the beginning of the next period.

https://demo.jiaopengzi.com/pbi/150-full.html

https://jiaopengzi.com/video/video-101

 

jiaopengzi_0-1683163909439.png

 

 

Here's the information you're looking for.

  1. In the future, please upload an attachment instead; I spent quite a while copying from it.
  2. 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.
  3. Note that your "Current Inventory" table should include a date dimension.
  4. 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 () )

 

 

jiaopengzi_1-1683282646414.png

 

jiaopengzi_0-1683282604490.png

 

pbi-url 

 

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

 

jiaopengzi_0-1683365650673.png

 

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?

MonthProductForecastReceipts04_Ending Inventory04_Ending Inventory_Display
01-05-2023AB52783009-22690
01-06-2023AB468672022472515
01-07-2023AB370838353732642
01-08-2023AB45562206-19770
01-09-2023AB408361771182094
01-10-2023AB 105111693145

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 () )

 

 

jiaopengzi_0-1683598489899.png

 

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? 

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.

 

jiaopengzi_0-1683647364002.png

 

 

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
    RESULT

 

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", [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

 

Thanks a ton.. will try it out in my dataset

thank you very much for sharing it.. but in your data example your forecast is always less than (Begining) + Receipts. Lets say your first Forecast is 114 instead of 14. Then the ending will be negative, how can we ensure we dont carry forward the negatives? 

Firewall's blocking your links. Can you please elaborate; does your logic capture the following case?

 

Beginning InvDemandReceiptsEnding Inventory
400100000
0100020001000

 

My obstacle is this edge case where Ending Inventory wants to go negative, in which case I need it to stay 0. For my purposes, we don't have a backlog, so "Negative Inventory" is just "Lost Sales", not "Backlog".

 

If I round up negative values to 0, then the numbers are still off because the demand and receipts are rolling. In other words, with the above case:

 

Inv = 400

Rolling Demand = 2000

Rolling Receipts = 2000

Ending Inventory = 400 + 2000 - 2000 = 400 (wrong, should be 1000).

Satya2804
Frequent Visitor

did you find a solution? Am struggling with a similar scenario..

Sorry, I gave up trying to find a solution that would allow me to forecast going to 0 and receiving the full amount in the future. Anything I could do in one function can only take me to 0, or negative, in which case the cumulative receipts don't add up properly.

 

I ended up settling for just keeping the visibility of when I'd go out of stock, and anything past that becomes an unknown.

Oh ok.. am still struggling to just use the previous week balance into next week blance calculation. How did you achieve that? Can you share the file or the measure logic please?

PaulDBrown
Community Champion
Community Champion

See if this explanation from the gurus at SQLBI helps:

https://youtu.be/m-qhcDcNKrE 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thank you for the video. Unfortunately it does not appear to solve my issue (I tried all 3 solutions to make sure). The video seems focused on finding more indirect-hidden circular depencies, whereas my issue could be classified as a "direct-visible circular discrepancy".

In other words, my calculated table has Column A referencing Column B, and Column B references Column A. But what's frustrating is that PowerBI seems to flag this as an issue, despite my code filtering out true discrepancies by making the reference sequential - by that I mean that Column A references column B from the previous row, and Column B references Column A from the current row.

 

Here's an example in Excel, to help illustrate what I mean:

 

Sequential Reference.png

Excel handles this just fine when I set up a test example. I need a way to achieve the same goal within PowerBI, because all of the fields I need to use for the calculation is generated in PowerBI.




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors