Forum Discussion

Funk-E-Guy's avatar
Funk-E-Guy
Icon for Helper II rankHelper II
5 years ago

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 previo...
Circular dependency
DAX
Inventory
Satya2804's avatar
Satya2804
Frequent Visitor
3 years ago

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? 

jiaopengzi's avatar
jiaopengzi
Frequent Visitor
3 years ago

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