Cálculo de Excesso de Estoque

[Edutra]

Olá pessoal, 

Estou com um problema de DAX e vou tentar explicar a lógica do indicador, que é simples, porém até chegar no resultado final várias medias são necessárias.

Estou calculando o quanto temos de excesso no estoque, ou seja criei um parâmetro onde o usuário ira definir acima de quantos dias de estoque ele vai querer considerar sobre-estoque. Criei a medida e deu certo, porém só funciona bem quando estou a nível de SKU, quando eu agrupo a análise a nível de grupo família, ou até coloco em cartão o valor fica zerado. Eu preciso que seja somado os resultados que estão a nível de SKU, ou seja uma análise a nível de linha, o problema é para chegar na medida de "cobertura de estoque"(ou dias de estoque), foram feitas várias contas de acordo com as regras do negócio, regras que consideram períodos específicos, canais de venda específicos, foi descontado as devoluções para fazer a média do valor vendido por dia. Ou seja, o contexto de linhas já perdeu nisso tudo. Cheguei a tentar criar uma coluna calculada e inserir a medida e depois fazer um SUM ou SUMX da coluna, enfim tentei muitas opções mas até agora não obtive o resultado que preciso.

Segue uma das fórmulas DAX que me deu o resultado esperado, porém de SKU:

Sobre-Estoque (R$) - Indicador =
    VAR varDiasEstoque = [Cobertura de Estoque - Indicador]
    VAR varMedia = [Média por Dia (R$) - Indicador]
    VAR varValorSobreEstoque =
        IF (
            varDiasEstoque < [Dias Estoque Simul. Med],
            0,
            varDiasEstoque - [Dias Estoque Simul. Med]
        ) * varMedia
    RETURN
        IF ( varValorSobreEstoque < 0, 0, varValorSobreEstoque )

Segue as medidas referenciadas nessa medida:

---- MODEL MEASURES BEGIN ----
MEASURE 'Dias Estoque'[Dias Estoque Simul. Med] = SELECTEDVALUE('Dias Estoque'[Dias Estoque Simul.], 240)
MEASURE Medidas[Cobertura de Estoque - Indicador] = DIVIDE([Estoque p Indicador],[Média por Dia (R$) - Indicador])
MEASURE Medidas[Estoque p Indicador] = 
CALCULATE(
    SUMX(FILTER(Estoque, VALUE(Estoque[deposito]) = 1), Estoque[em_estoque] * Estoque[custo_do_item]),
    Produto[Dias 180 até Hj] > 0,
    Produto[Data Primeira Compra] < EDATE(TODAY(), -3)
)
MEASURE Medidas[Média por Dia (R$) - Indicador] = 
VAR varAnterior3m = 
FILTER(
    Produto,
    Produto[Data Primeira Compra] < EDATE(TODAY(),-3)
)
VAR varTabelaVirtual = 
    ADDCOLUMNS(
        varAnterior3m,
        "cod_produto", Produto[codigo_produto],
        "custo", Produto[custo_do_item],
        "data_primcompra", Produto[Data Primeira Compra],
        "Venda 180", [Qtd. Vendida 180 dias(s licitação)],
        "Dias 180", IF(Produto[Data Primeira Compra] < (TODAY()-180), 180, TODAY()-Produto[Data Primeira Compra])
    )
VAR varMediaDia = 
    SUMX(FILTER(varTabelaVirtual, [Venda 180] >= 0),  [custo]*[Venda 180]/[Dias 180])

RETURN
varMediaDia
MEASURE Medidas[Qtd. Vendida 180 dias(s licitação)] = 
CALCULATE(
    [Qtd. Faturada Líq.],
    DATESINPERIOD(
        'Calendário'[Data],
        TODAY(),
        -180,
        DAY
    ),
    FILTER(ALL(Canal_de_venda), OR(VALUE(Canal_de_venda[codigo_canal_venda]) = 1, VALUE(Canal_de_venda[codigo_canal_venda]) = 2)
))
MEASURE Medidas[Qtd. Faturada Líq.] = [Qtd. Faturada Bruta]-[Qtd. Devolvida - Saída]
MEASURE Medidas[Qtd. Devolvida - Saída] = SUM(Nota_fiscal_saida_devolucao[qtd])
MEASURE Medidas[Qtd. Faturada Bruta] = SUM(Itens_Nota_fiscal_saida[qtd])
---- MODEL MEASURES END ----

[Edutra]

Consegui resolver essa questão apenas utilizando coluna calculada, alterei as fórmulas para que se adaptassem ao contexto, depois criei uma media com um SUM para agrupar as informações dessa coluna, e fiquei tudo certinho. Vou tentar converter essa coluna calculada em uma medida, para melhorar meu modelo, mas por enquanto já resolveu meu problema. Obrigado a todos pela ajuda.

[Edutra]

21 minutos atrás, Rafa Lemos disse:

@Edutra entendo perfeitamente.
É que se temos uma base fica mais fácil de testarmos.

Consegue pelo menos colocar como estão os relacionamentos e as colunas das tabelas utilizadas?

Para calcular esse sobre estoque utilizo

 - Valor faturado liquido(descontando as devoluções) dos últimos 180 dias, divido esse valor a nível de linha pela quantidade de dias corridos, que será ou 180 ou caso o produto for mais recente que isso, a quantidade de dias desde a primeira venda desse produto. Porém, eu quero analisar apenas produtos que tem mais de 3 meses desde a primeira venda. Com isso em mãos eu já tenho o valor médio faturado por dia corrido. Isso precisa ser calculado a preço de custo atual, conforme o custo que está na dimensão Produto.

-Agora eu após calcular o valor de estoque atual, com um simples SUMX. Porém apenas produtos que tenham mais de 3 meses de "vida"

-Depois disso eu calculo a cobertura de estoque, dividindo esse valor de estoque pelo custo médio consumido por dia.

Até aqui tudo está funcionando OK. Porém complicou quando eu  quis calcular os dias extras,  de estoque, que funcionou ok no nível menor da granularidade. Onde verifiquei os produtos que tinham os dias do estoque acima do valor definido no parâmetro "Dias Estoque Simul", e multipliquei o quantidade de dias que estava acima desse parâmetro pelo o valor médio por dia calculado anteriormente. O total geral precisa ser o resultado da soma de cada item. Atualmente está zerado porque os dias de estoque geral está menor que o parâmetro.

 

 

[Rafa Lemos]

@Edutra consegue mandar uma base de exemplo e o resultado esperado?
Assim conseguimos pensar melhor em como fazer.

[Edutra]

34 minutos atrás, Rafa Lemos disse:

@Edutra consegue mandar uma base de exemplo e o resultado esperado?
Assim conseguimos pensar melhor em como fazer.

@Rafa Lemos fica realmente complicado, estou conectado no DW, e existem pelo menos 6  tabelas envolvidas nessas medidas.

Qualquer dúvida sobre o processo estou a disposição, mas uma base modelo iria complicar bastante.

[Rafa Lemos]

@Edutra entendo perfeitamente.
É que se temos uma base fica mais fácil de testarmos.

Consegue pelo menos colocar como estão os relacionamentos e as colunas das tabelas utilizadas?