Coluna Adicional de Subtração Condicional

[HelioVitor]

Olá pessoal!

Estou com uma grande dificuldade em adicionar uma coluna de subtração condicional por linguagem M no Power Bi. Espero que possam me ajudar...

A ideia é que seja criada uma coluna que mostrará a subtração da coluna "Estoque Total Disp." pela coluna "Quantidade", porém essa subtração deve seguir alguns critérios:

Deve ser uma subtração acumulativa de acordo com o contexto de "MP" igual e deve ocorrer considerando o início da subtração para  "Data Início - SAP" mais recentes até as "Data Início - SAP" mais posteriores...

Portanto, nesse exemplo em questão a coluna condicional deveria ser da seguinte forma:

Obrigado pelo suporte !!

[Erick Oliveira]

Boa tarde Helio;

Verifique se o código abaixo atende, basta joga-lo em uma consulta nula e adaptar caso necessário:

let
    Fonte = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "xdNNCsMgEAXgu7geyPw8dTyLeP9r1DSLNsEWm6ZUXIzw+RRGaw2KhWVRVgsUIvcBKb00uBESegk4h0Y1GO8thEvupW9r/wDJVagL1rNIXMkyzkn9q7SxVBSKKJfKwelxWuZvMwf3fJE5L5miPjG1/UPLWYwfTOCglDaKMRVAyIFJPkqOP6F5mh4/7ht66JWs895V5XVHaO0G",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [#"Data Início - SAP" = _t, MP = _t, Quantidade = _t, #"EstoqueTotalDisp." = _t]
    ),
    TabelaClassificada = Table.TransformColumnTypes(
        Fonte,
        {
            {"Data Início - SAP", type date},
            {"MP", Int64.Type},
            {"Quantidade", type number},
            {"EstoqueTotalDisp.", type number}
        }
    ),
    estoque = List.Buffer(List.ReplaceValue(TabelaClassificada[#"EstoqueTotalDisp."], null, 0, Replacer.ReplaceValue)),
    quantidade = List.Buffer(List.ReplaceValue(TabelaClassificada[Quantidade], null, 0, Replacer.ReplaceValue)),
    grupo = List.Buffer(TabelaClassificada[MP]),
    GerarLista =
        let
            SubtracaoAcumulada = List.Generate(
                () => [
                    // Variavel que irá acumular
                    SA = estoque{0},
                    i = 0
                ],
                each [i] < List.Count(estoque),
                each
                    try
                        if grupo{[i]} = grupo{[i] + 1} then
                            [
                                SA = [SA] - quantidade{[i]},
                                i = [i] + 1
                            ]
                        else
                            [
                                SA = estoque{[i] + 1},
                                i = [i] + 1
                            ]
                        otherwise [
                            i = [i] + 1
                        ],
                each [SA] - quantidade{[i]}
            )
        in
            SubtracaoAcumulada,
    ExpandirTabela = Table.FromColumns(
        {
            TabelaClassificada[#"Data Início - SAP"],
            TabelaClassificada[#"EstoqueTotalDisp."],
            TabelaClassificada[Quantidade],
            TabelaClassificada[MP],
            GerarLista
        },
        {"Data Início - SAP", "EstoqueTotalDisp.", "Quantidade", "MP", "Acumulado"}
    )
in
    ExpandirTabela

 

[HelioVitor]

7 horas atrás, Erick Oliveira disse:

Boa tarde Helio;

Verifique se o código abaixo atende, basta joga-lo em uma consulta nula e adaptar caso necessário:

let
    Fonte = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "xdNNCsMgEAXgu7geyPw8dTyLeP9r1DSLNsEWm6ZUXIzw+RRGaw2KhWVRVgsUIvcBKb00uBESegk4h0Y1GO8thEvupW9r/wDJVagL1rNIXMkyzkn9q7SxVBSKKJfKwelxWuZvMwf3fJE5L5miPjG1/UPLWYwfTOCglDaKMRVAyIFJPkqOP6F5mh4/7ht66JWs895V5XVHaO0G",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [#"Data Início - SAP" = _t, MP = _t, Quantidade = _t, #"EstoqueTotalDisp." = _t]
    ),
    TabelaClassificada = Table.TransformColumnTypes(
        Fonte,
        {
            {"Data Início - SAP", type date},
            {"MP", Int64.Type},
            {"Quantidade", type number},
            {"EstoqueTotalDisp.", type number}
        }
    ),
    estoque = List.Buffer(List.ReplaceValue(TabelaClassificada[#"EstoqueTotalDisp."], null, 0, Replacer.ReplaceValue)),
    quantidade = List.Buffer(List.ReplaceValue(TabelaClassificada[Quantidade], null, 0, Replacer.ReplaceValue)),
    grupo = List.Buffer(TabelaClassificada[MP]),
    GerarLista =
        let
            SubtracaoAcumulada = List.Generate(
                () => [
                    // Variavel que irá acumular
                    SA = estoque{0},
                    i = 0
                ],
                each [i] < List.Count(estoque),
                each
                    try
                        if grupo{[i]} = grupo{[i] + 1} then
                            [
                                SA = [SA] - quantidade{[i]},
                                i = [i] + 1
                            ]
                        else
                            [
                                SA = estoque{[i] + 1},
                                i = [i] + 1
                            ]
                        otherwise [
                            i = [i] + 1
                        ],
                each [SA] - quantidade{[i]}
            )
        in
            SubtracaoAcumulada,
    ExpandirTabela = Table.FromColumns(
        {
            TabelaClassificada[#"Data Início - SAP"],
            TabelaClassificada[#"EstoqueTotalDisp."],
            TabelaClassificada[Quantidade],
            TabelaClassificada[MP],
            GerarLista
        },
        {"Data Início - SAP", "EstoqueTotalDisp.", "Quantidade", "MP", "Acumulado"}
    )
in
    ExpandirTabela

 

Boa noite, Erick, obrigado pelo rápido retorno!

Deu perfeitamente certo. Fantástico, fiz alguns ajustes e ficou 100%. Muito obrigado pela ajuda!!