Conditional and selective sum with filters

[ClaudioF]

Hey guys! how you doing?

Just got an insight in my dax code, i've been working on the logic of it for a while and i think im getting somewhere, but still need help. I need the code to return the "out" and "available" status correctly for each order, in a way that it will always start with the earliest order then to the oldest. the reult i got for this actual data is right, but it changes when i change the values, getting wrong results, here is an example of the right result i got.:

I have used many calculations and contitions to achieving this result, but the logic is still not complete.

look at the result i get when change the values: 

The correct results for this last one would be available, out, available, out, out, available, out, out. 

The measure i am using is the "FinalCorrectStatus" you can find it in the file: DistributeStok 

The code is:

FinalCorrectStatus =

VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto])

VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido])

VAR StockAvailable =

    CALCULATE(

        SUM('Table Estoq'[Estoque Real]),

        'Table Estoq'[Produto] = CurrentItem

    )

//soma normal soma sem condição cumulativamente

VAR SomaNormal =

    SUMX(

        FILTER(

            ALLSELECTED('DB_ORDERS'),

            'DB_ORDERS'[Pedido] <= CurrentOrderNum &&

            'DB_ORDERS'[Produto] = CurrentItem

        ),

        'DB_ORDERS'[Quant. Falta]

    )

//Filtro de apoio para a SomaMaiorEstoq

VAR SomaMaiorApoio =

    FILTER(

        ALLSELECTED('DB_ORDERS'),

        'DB_ORDERS'[Pedido] <= CurrentOrderNum &&

        'DB_ORDERS'[Produto] = CurrentItem

    )

//soma apenas quando o valor da "SomaNormal" for maior que o estoque

VAR SomaMaiorEstoq =

    SUMX(

        ADDCOLUMNS(

            SomaMaiorApoio,

            "@Acumulado",

            SUMX(

                FILTER(

                    SomaMaiorApoio,

                    'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido])

                ),

                'DB_ORDERS'[Quant. Falta]

            )

        ),

        VAR TotalAcumulado = [@Acumulado]

        RETURN

            IF(TotalAcumulado > StockAvailable, 'DB_ORDERS'[Quant. Falta], 0)

    )

//diferenca entre A soma cumulativa normal e a soma apenas dos maiores que o estoque

VAR Diferenca =

    SomaNormal - SomaMaiorEstoq

//valor resultante de "Diferenca" + a quantidade do item no pedido

VAR Dif_mais_QTFALT =

    Diferenca + SELECTEDVALUE('DB_ORDERS'[Quant. Falta])

//verifica se o estoque já atingiu seu limite, vai somando o que estiver dentro da condição de caber no estoque

VAR SomaFinal =

    SUMX(

        FILTER(

            ALLSELECTED('DB_ORDERS'),

            'DB_ORDERS'[Pedido] <= CurrentOrderNum &&

            'DB_ORDERS'[Produto] = CurrentItem &&

            (

                SUMX(

                    FILTER(

                        ALLSELECTED('DB_ORDERS'),

                        'DB_ORDERS'[Pedido] <= CurrentOrderNum &&

                        'DB_ORDERS'[Produto] = CurrentItem

                    ),

                    'DB_ORDERS'[Quant. Falta]

                ) <= StockAvailable ||

                (Diferenca + 'DB_ORDERS'[Quant. Falta]) <= StockAvailable

            )

        ),

        'DB_ORDERS'[Quant. Falta]

    )

RETURN

    IF(

        ISBLANK(StockAvailable),

        "out",

        IF(

            SomaFinal <= StockAvailable &&

            (Dif_mais_QTFALT <= StockAvailable),

            "available",

            "out"

        )

    )

its very complex, and i am almost giving up..

Can anyone help me please?

Thankyou

[v-sdhruv]

Hi @ClaudioF ,
Just wanted to follow up if you were able to resolve the issue or you need any assistance?
If you were able to resolve the issue, can you post your solution so that it would benefit other members to reach the solution easily.
Thank You

[v-sdhruv]

Hi @ClaudioF ,
Just wanted to follow up if you were able to resolve the issue or you need any assistance?
If you were able to resolve the issue, can you post your solution so that it would benefit other members to reach the solution easily.
Thank You

[v-sdhruv]

Hi @ClaudioF ,
Just wanted to follow up if you were able to resolve the issue or you need any assistance?
If you were able to resolve the issue, can you post your solution so that it would benefit other members to reach the solution easily.
Thank You

[bhanu_gautam]

@ClaudioF , Try using

FinalCorrectStatus =
VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto])
VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido])

VAR StockAvailable =
CALCULATE(
SUM('Table Estoq'[Estoque Real]),
'Table Estoq'[Produto] = CurrentItem
)

VAR OrdersTable =
FILTER(
ALLSELECTED('DB_ORDERS'),
'DB_ORDERS'[Produto] = CurrentItem
)

VAR RunningTotal =
SUMX(
FILTER(
OrdersTable,
'DB_ORDERS'[Pedido] <= CurrentOrderNum
),
'DB_ORDERS'[Quant. Falta]
)

VAR PreviousRunningTotal =
SUMX(
FILTER(
OrdersTable,
'DB_ORDERS'[Pedido] < CurrentOrderNum
),
'DB_ORDERS'[Quant. Falta]
)

RETURN
IF(
ISBLANK(StockAvailable),
"out",
IF(
PreviousRunningTotal < StockAvailable,
IF(
RunningTotal <= StockAvailable,
"available",
"out"
),
"out"
)
)

[ClaudioF]

Sorry it didnt work, the result is the same;