Medida dax para diluir base de acordo com contexto

[Lucas Eduardo Gonçalves De Andrade]

Estou com dúvida como fazer uma Dax para esse projeto de power bi que estou criando, preciso criar uma medida onde ela considera o contexto de grupo, unidades, produtos, data(meses) para realizar um cálculo de diluição de de uma base de contratos em meses do atual para futuros meses de acordo com esse contexto citado.

 

Tenho a seguinte situação, gerei uma produção prevista de acordo com cálculos feitos, após eu subtraio as Vendas MI que considero como prioridade na redução de Produção prevista, após esse cálculo quero reduzir as Vendas ME de acordo com uma coluna de candência que significa se posso adiantar a produção ou não, e preciso que seja usada no contexto também de grupo, unidades, produtos, data(meses). Na tabela de contratos liberados ontem contém a base que iriei usar, tenho as apenas a data final de produção, porém tenho contratos que viraram o mês em produção, em caso que tenho contratos que podem ser antecipados quero que minha disponibilidade fiquem no valor 0 até que acabe o saldo conforme o excel. Caso o campo de candência seja "SIM", quero que considere o contexto de data final de produção como o mês onde vai abater a disponibilidade.

 

Segue excel da situação atual com apenas o relacionamento de mês e ano com a Calendário

 

Segue exemplo do resultado, vale ressaltar que tenho várias unidades e vários produtos que precisam serem calculados corretamente, não achei nada de exemplo na internet, quem puder ajudar, agradeço.

 

Segue modelo do power bi!

Estou aberto a sugestões de soluções, e dicas!

 

Vendas.pbix

Editado Fevereiro 20 por Lucas Eduardo Gonçalves De Andrade
correção

[Erick Oliveira]

@Lucas Eduardo Gonçalves De Andrade;

Baseado no que descreveu, você precisa de uma medida que olhe para o resultado de "PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA" e avalie se o resultado é menor que zero, caso seja, ele ira jogar o valor para o mês posterior daquele produto naquela unidade. O problema disso é que, após jogar para o próximo mês, o resultado dessa próxima soma deverá ser avaliado novamente, pois caso ainda seja negativo, a medida deverá repetir esse  processo até que o valor seja maior ou igual a zero.

Percebe que existe uma recursão acontecendo, a função precisa do resultado dela mesma pra continuar calculando, e atualmente usando linguagem DAX não é possível fazer esse tipo de operação.

Mas pensando em uma solução, é possível realizar essa cálculo no Power Query, porém, você teria que calcular o valor da medida "PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA" lá. Fiz isso com uma amostra da sua base:

Criei uma função personalizada chamada fxDiluirValores que faz a regra descrita:

// fxDiluirValores
(values as list) as list =>
    let
        tamanhoLista = List.Count(values) + 1,
        somaAcumulada = List.Generate(
            () => [
                // Variavel que irá acumular
                SA = 0,
                i = 0
            ],
            each [i] < tamanhoLista,
            each
                try
                    if [SA] < 0 then
                        [
                            SA = [SA] + values{[i]},
                            i = [i] + 1
                        ]
                    else
                        [
                            SA = values{[i]},
                            i = [i] + 1
                        ]
                    otherwise [
                        i = [i] + 1
                    ],
            each if [SA] < 0 then if [i] = tamanhoLista - 1 then [SA] else 0 else [SA]
        )
    in
        List.Skip(somaAcumulada, 1)

Depois chamei essa função na base com o valor da medida:

let
    Fonte = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "pZVRTsMwDECvUlXib9USJ07SzwCFVRotWreCNO0wHGAX4Dq9GC2IBJouirfPVHr2s2Onx2Ne7+3w2dh8lT/ZXd1sbHbf9nVjsyKTeDd+ZnzNYA0M5HgohFYCWX5aJZLil+TKCGAEUjqyNIAJpAlsJTAETAe9rEDFKCm9LCpIIJGFrdU00MkSEzpVYy5zj1X2WjW2G0kTqgJKfrGvi6iTFUDN6nzVdCURtLPN86Gau3KOnEVc/1Fe8/LAzRnnJ7SO+vXDuXuodt/lbdu3zHabuawR6qoAzrvg8koHSQ8xHmxfTaHUwkvBuRJlpPHxOK4iuMXGz44o6WF0UBSH+AjGw/ia8JYwriiILUR3GM4/mx/eDWdMGp4OOnE0SMroVLVxqvtpeYaP+drX79VUJQSyFEgQoIWRBTb9aSisXzyBkFZh2JxCMJXGhhNZaENE/7wVSE0rqWn1wn8OFMbQzm5fbDunSh5cTLutWpcryCJlKLhESD8ypy8=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [UNIDADES = _t, PRODUTOS = _t, #"DATE - Mês" = _t, #"PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA" = _t]
    ),
    TipoAlterado = Table.TransformColumnTypes(
        Fonte,
        {
            {"UNIDADES", type text},
            {"PRODUTOS", type text},
            {"DATE - Mês", type date},
            {"PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA", Int64.Type}
        }
    ),
    ValorSubstituido = Table.ReplaceValue(TipoAlterado,null,0,Replacer.ReplaceValue,{"PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA"}),
    LinhasAgrupadas = Table.Group(
        ValorSubstituido,
        {"UNIDADES", "PRODUTOS"},
        {{"Tabela", each _, type table [#"DATE - Mês" = date, #"PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA" = number]}}
    ),
    AcumularTabela = Table.AddColumn(
        LinhasAgrupadas,
        "Tabela Ajustada",
        each
            let
                _funcaoDiluir = fxDiluirValores(List.Buffer([Tabela][#"PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA"])),
                _listaDatas =[Tabela][#"DATE - Mês"],
                _tabelaFinal = Table.FromColumns(
                    {_listaDatas, _funcaoDiluir}, {"DATE - Mês", "PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA"}
                )
            in
                _tabelaFinal,
        type table [#"DATE - Mês" = date, #"PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA" = number]
    ),
    TabelaAjustadaExpandido = Table.ExpandTableColumn(AcumularTabela, "Tabela Ajustada", {"DATE - Mês", "PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA"}, {"DATE - Mês", "PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA"}),
    ColunasRemovidas = Table.RemoveColumns(TabelaAjustadaExpandido,{"Tabela"})
in
    ColunasRemovidas

 

Feito isso valor fica de acordo com a imagem que enviou:

 

Não consegui visualizar um cenário realizando essa operação via DAX.

Medida dax para diluir base de acordo com contexto.pbix

[Erick Oliveira]

Em 20/02/2024 em 21:40, Erick Oliveira disse:

Mas pensando em uma solução, é possível realizar essa cálculo no Power Query, porém, você teria que calcular o valor da medida "PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA" lá. Fiz isso com uma amostra da sua base:

Os valores não vão atualizar mesmo, a tabela fBase é uma tabela imputada manualmente que utilizei só para demonstrar a dinâmica, pra dar certo você precisa fazer isso com sua fonte de dados real, levando alguns valores para o Power Query.

[Erick Oliveira]

Bom dia @Lucas Eduardo Gonçalves De Andrade;

As imagens que enviou são iguais, poderia enviar um exemplo do resultado que espera?

[Lucas Eduardo Gonçalves De Andrade]

Segue

[Lucas Eduardo Gonçalves De Andrade]

Obrigado,

Vou avaliar, nesse cenário consigo de alguma forma ter uma medida de Vendas ME calculada corretamente e mostrando em visualização os contratos dentro dela ?

[Erick Oliveira]

@Lucas Eduardo Gonçalves De Andrade;

Sendo sincero não tinha visto que tinha uma regra para vendas, e não consegui entender apenas com a imagem. Poderia detalhar mais? Provavelmente teria que criar outra função para vendas, mas seguindo a mesma ideia.

[Lucas Eduardo Gonçalves De Andrade]

Vou tentar detalhar melhor o objetivo dessa medida ou necessidade de solução.

Estou fazendo um projeto de disponibilidade de produtos com finalidade sinalizar para Comercial sobre os volume comercializados e volumes disponíveis para venda.

PRODUÇÃO PREVISTA = Foi um cálculo que realizei de acordo com estimativas de produção de acordo com calendário

VENDAS MI = Tenho uma base de pedidos e estoque que faço um cálculo para obtiver o que tenho em aberto ainda para produzir.

VENDAS ME que é o foco do problema = Tenho uma tabela chamada Contratos_Consolidado_Liberados que contém volume e todas ligações de PRODUTO, UNIDADES e  DATA FINAL PREVISTA - PRODUÇÃO, o grande "X" da questão que tenho contratos que viram o mês em produção e contratos que são exclusivamente produzidos apenas no mês da coluna  DATA FINAL PREVISTA - PRODUÇÃO, para essa diferenciação criei a coluna Cadência com finalidade de fazer a separação no contexto.

Preciso de certa forma que essa medida VENDAS ME considera a diluição considerando

PRODUÇÃO PREVISTA - VENDAS MI SE RESULTAR MAIOR QUE 0 

VENDAS ME REDUZA ESSE VOLUME ATÉ QUE ELE RESULTE EM = 0 e se SOBRAR SALDO DE VENDAS, PASSE PARA O PRÓXIMO MÊS ATÉ QUE O SALDO DE ME SAI DO COTEXTO DE DATA QUE TEM NA TABELA OU FINALIZE SEU SALDO POR INTEIRO 

Na sequencia lógica do cálculo da PRODUÇÃO PREVISTA DISPONÍVEL P/ VENDA seja considerado esse contexto para a lógica

Essa é a situação atual. Note que VENDAS ME está com valor acima da disponibilidade após o cálculo da (Produção prevista - Vendas MI), gerando uma quantidade negativa.  

Essa é o objetivo, Vendas ME considerar apenas o saldo restante do cálculo da (Produção prevista - Vendas MI) e jogar o restante para o próximo mês onde se finaliza o saldo de Vendas ME.

No lugar de vendas ME, queria uma Dax porque quero mostrar os dados que estão dentro da medida.

O correção maior é na VENDAS ME pois ela corrigi o resultado da medida PRODUÇÃO PREVISTA DÍSPONÍVEL P/ VENDA.

Vou deixar novamente o modelo.Vendas.pbix

Editado Fevereiro 21 por Lucas Eduardo Gonçalves De Andrade

[Erick Oliveira]

Se entendi bem, criei uma medida que ajusta esse valor de Vendas com base na medida balanceada anteriormente:

VENDAS ME AJUSTADO = 
VAR _Producoes = [PRODUÇÃO PREVISTA DÍSPONÍVEL PARA VENDA] - [PRODUÇÃO PREVISTA DÍSPONÍVEL PARA VENDA AJUSTADA]
RETURN
    IF(_Producoes<0, -_Producoes, _Producoes + [QUANTIDADE TOTAL DE EXPORTAÇÃO] )

 

Se isso ainda não for o que espera, recomendo que envie um excel com formulas descrevendo as regras.

Vendas.pbix

[Lucas Eduardo Gonçalves De Andrade]

E o que preciso, porém a relação de data impactou no resultado em outra situação, preciso que o saldo exemplo de verde, da coluna seja redistribuído no mês seguinte independente da data de produção que está atualmente com relacionamento ativo, no caso em amarelo o saldo ficou diferente devido esse motivo. Realmente não tenho ideia de como pode fazer com ou sem o relacionamento entre a coluna de data.

 Após esse ajuste, acredito que entenda a necessidade completamente. Eu atualizei a base, segue atualizado:Vendas.pbix

[Erick Oliveira]

@Lucas Eduardo Gonçalves De Andrade, o problema agora está na sua base e no modelo. Abaixo algumas coisas que percebi:

 

Tabela de datas está começando no dia 16, sendo que tem vendas MI para dia 01.

 

Os valores mudaram comparando o arquivo atual e o arquivo anterior que mandei, então independente dos relacionamentos, os valores estarão diferentes:

Antes:

Agora:

 

Tem uma data com ano de 2054:

[Lucas Eduardo Gonçalves De Andrade]

Mudou sim, pq atualizei o modelo, mas da mesma forma ele trouxe o resultado esperado, ele deve jogar o restante para mês posterior e não aconteceu.

Atualizei o modelo para ver se no contexto, ele iria atender.

Essa data de 2054, vou tirar da base.

Editado Fevereiro 23 por Lucas Eduardo Gonçalves De Andrade

[Tiago Craici - Xperiun]

@Lucas Eduardo Gonçalves De Andrade

 

Caso atendido seu objetivo, por gentileza marque a Melhor resposta para o tópico, para isso basta ir naquela que mais te ajudou e clicar nas opções conforme print: