Looping no DAX - Estrutura de repetição

[Alan Pacheco]

Boa noite,,
Amigos da comunidade gostaria de uma ajusta de vocês em uma determinada situação ( Lembrando que o exemplo em excel é a estrutura de 3 tabelas gigantes que eu disponho)
Eu gostaria de realizar o seguinte calculo,
ESTOQUE + COMPRA - CONSUMO,  porém em uma semana o qual eu não tenha ESTOQUE eu quero projetar para frente, seguindo o seguinte exeplo.
Se ESTOQUE = (vazio) eu quero que ele pegue o resultado do estoque final da semana anterior e assim projetando para as semanas a frente.

Abaixo segue o printo da resolução e a tabela no Excel, se possível gostaria que me ajudassem a fazer em dax, vi alguns posts em comunidades o qual é replicado o While que seria a minha necessidade, porém não entendi muito bem.

Desde já agradeço se alguém puder me ajudar a resolver esse problema em DAX.

Resalto que as 3 tabelas são separadas
 

DaxLoop.xlsx

[Rodrigo R1]

Boa tarde @Alan Pacheco. Tudo bem amigo?

Aproveitei agora a tarde para revisar a sua dúvida e postar uma solução definitiva para o seu case.

Peguei o arquivo Excel que você enviou e criei o seguinte modelo:

Veja que é um modelo simples, star schema, com relacionamento de 1 : * onde dimensão filtra fato - seguindo as boas práticas.

Aproveitei e usei um modelo de dcalendario que o Leo disponibilizou na Live 52 - ela é super completa - eu a utilizo sempre em meus modelos e recomendo utiliza-la.

Perceba que a coluna de datas das 3 tabelas fato estão no formato data dd/mm/aaaa - isso para garantir a integridade dos relacionamento, visto que a granularidade da dcalendario é nesse mesmo formato de data também.

Essa é uma etapa de extrema importante, pois se você deixar as tabelas fato no formato de semana e depois criar o relacionamento entre a dcalendario e as tabelas fatos, usando a coluna de semana, irá criar relacionamentos muitos para muitos (* : *) e isso poderá não trazer os resultados esperados.

Após realizar todas as etapas acima, vamos para as medidas:
 

Compras = sum(fCompra[Compra])

Consumo = sum('fCons  Med'[Consumo Méd Ultimas 5 semanas])

Estoque = Sum(fEstoque[Estoque])

Resultado = [Estoque] + [Compras] - [Consumo]
 

Estoque Final R1 =

var vmaxcompra = MAX(fCompra[Data])

var vmaxconsumo = Max('fCons  Med'[Data])

var vmaiordata = Max(vmaxcompra, vmaxconsumo)

var vdatamax = EOMONTH(vmaiordata,0)

 

var vultimoregistroestoque = CALCULATE(max(fEstoque[Data]),

                        dCalendario[Data] <= vdatamax)

 

var vresultado = CALCULATE([Resultado],

                            dCalendario[Data] = vultimoregistroestoque)

 

var vacumulado = CALCULATE(

                    [Compras] - [Consumo],

                    dCalendario[Data] <= vdatamax &&

                    dCalendario[Data] > vultimoregistroestoque)

return

if([Estoque] = BLANK(), vresultado + vacumulado, vresultado)

Jogando os dado em uma tabela, teremos seguinte resultado:

E veja que independente do formato de semana a ser apresentado, ou até mesmo demonstrar por mês, o resultado é o mesmo.

Por fim, a minha sugestão é você revisar o modelo de dados, pois ele pode não estar seguindo as boas práticas e com isso a medida acima pode não apresentar o comportamento desejado.

Segue o PBIX em anexo.

Espero que tenha conseguido ajudar novamente e que você possa encontrar sua solução, pois sei que você está buscando isso há alguns dias.

Sucesso aí, amigo....

Solução Alan Forum.pbix

[Renato Carlos Rossato]

Olá Alan, hoje o Léo postou um vídeo que tem uma situação parecida onde ele usa variáveis pra pegar dados da última semana que teve movimentação, acredito que dê pra adaptar aí:

https://youtu.be/TsehQrZ4P2E

Se tiver dificuldades chama no Whats no 67 991586342.

 

[Alan Pacheco]

Boa noite!
@Renato Rossato eu já vi essa aula dele na hora que ele postou, mas não é exatamente o que eu estou necessitando não meu amigo, da uma olhada no arquivo e na forma o como detalhei,
vou te mostrar em um blogue de uma comunidade mais ou menos o que eu preciso fazer, pois diferente do Leo, eu não quero que repita o ultimo valor, eu quero projetar semanas para frente considerando o calculo anterior, que é uma estrutura de repetição.

Detalhe eu já consegui fazer isso em python, utilizando looping, porém infelizmente na empresa não tenho como acessar do SQL com python, pois ainda não tenho uma grande interface com a T.I

https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314

Neste link tem o conceito, eu só não consegui aplicar no que eu to propondo.
 

Editado Agosto 17, 2022 por Alan Pacheco

[Rodrigo R1]

Bom dia, @Alan Pacheco. Tudo bem amigo.

Segue minha sugestão para a solução do seu case.

Primeiro foram criadas as medidas de estoque, compras e consumo médio - que nada mais são do que a soma das colunas respectivas:

Compra R1 = sum(Compra[Compra])

Estoque Inicial R1 = Sum(Estoque[Estoque])

Cons Medio R1 = sum('Cons  Med'[Consumo Méd Ultimas 5 semanas])

 

Depois eu criei uma resultado - que é a regra do seu negócio:

Resultado R1 = [Estoque Inicial R1] + [Compra R1] - [Cons Medio R1]

 

E por fim a medida de estoque final:

Estoque Final R1 =

var maiordata = max(dcalendario[Date])

var ultimolancamento = CALCULATE(Max(Estoque[Semana]),

                       dcalendario[Date] < maiordata)
 

return

if([Estoque Inicial R1] = BLANK(),

CALCULATE([Resultado R1],

dcalendario[Date] = ultimolancamento) +

CALCULATE([Compra R1] - [Cons Medio R1],

dcalendario[Date] <= MAX(dcalendario[Date]) &&

dcalendario[Date] > ultimolancamento), [Resultado R1])

Nessa medida se o estoque é blank, ela vai pegar o último valor da medida resultado (ultimo saldo estoque) com base na última data da tabela estoque, que existia dados.
Depois irá somar com o acumulado da diferença entre compra e consumo médio, posterior a essa mesma última data da tabela de estoque que existia dados.

Aí teremos o seguinte resultado:

Segue PBIX com a solução.

Espero que tenha conseguido ajudar e se minha resposta foi a solução para o seu case, gentileza marca-la para fechar o tópico.

Sucesso aí, amigo....

Dúvida forum Alan Pacheco.pbix

[Alan Pacheco]

Fala @Rodrigo R1 mestre, a medida até pe boa, porém o teu conceito que tu fez pegando a minha base tu alocou a nível de mês/ano e o que de fato eu precisaria era da medida por semana eu quero vizualizar a nível de semana e quando eu faço isso o valor distorce totalmente do da minha realidade olhando a nível semana.
Vou mandar um exemplo mais apurado para ver se consigo clarear para se possível você tentar me ajudar.

Acredito que o maior entrave é o calculo ser por semana.
Caso consiga me ajudar fico extremamente grato, pois como eu informei acima eu só vi em um bloque e a estrutura while que é mencionada no bloque que o rapaz lá resolve é bem parecido com algo que eu necessito, visto que estou buscando uma informação a nível semana, então se for fazer a dCalendario tem que conter a semana até porque a forma que irei apresentar é semanal.

Abaixo segue o novo exemplo.

Desde já te agradeço d+ e se puder continuar me ajudando não tenho nem palavras para descerver o quanto fico feliz de ver a glr tentando da mesma forma que eu, pois já to a quase 3 semanas levando pisa com isso.

OBSERVAÇÃO: Tu vais notar que a medida apresenta estar correta porém eu necessito considerar as datas apenas da dCalendario, pois no meu caso real eu utilizo a segunda feira de cada semana do ano para me retornar o estoque.

com isso ele acaba não interpretando quando utilizo no meu calculo real a data no estoque.


 

DaxLoop.xlsx

Editado Agosto 17, 2022 por Alan Pacheco

[Alan Pacheco]

Acrescentando o resultado deveria aprecer seria o valor 881.822, mas quando util
Se você notar no calculo ali que necessito tens de fato uma tentativa minha que seria o valor correto então basicamente seria assim o meu estoque final da semana anterior passaria a ser o meu estoque inicial da semana seguinte.

[Rodrigo R1]

Boa noite, @Alan Pacheco.

 

Se a sua granularidade de dados é por semana, basta você trocar na variável "maiordata" para maior semana usando como valor a maxima semana da sua dcalendário.

Então a nova variável ficaria assim:

maiorsemana = max(dcalendario[contadorsemana])

E onde for dcalendario[date] você troca por dcalendario[contadorsemana])

O restante fica igual....pois a lógica é considerar períodos anteriores ao contexto - seja ele dia, semana, mês ou ano.

Só seguir esse raciocínio que vai dar certo, amigo.

Faz um teste aí e manda o resultado aqui pra gente conferir.

[Alan Pacheco]

Fala Mestre, acabei de realizar o calculo Rodrigo e o mesmo ainda continua dando valores fora do contexto.
Ela faz diminuir ainda mais os valores, ressalto que ainda tentei utilizar a dcalendario semana do ano sem ser o contador de semana.

@Rodrigo R1 Segue o calculo com a medida.
O que ele ta fazendo é diminuir apenas a (quantidade - consumo) ele não ta considerando o resultado anterior que seria 881.822
Ressalto ainda contigo que eu já tinha chegado neste mesmo problema 😞 em outra medida, só aqui eu já fiz umas 8 medidas tentando fazer esse calculo.

Editado Agosto 17, 2022 por Alan Pacheco

[Rodrigo R1]

Alan,

A segunda variável precisa considerar o Max da semana da sua tabela de estoque - Você está usando da dcalendário.

Corrija esse ponto, por favor e faça o teste novamente.

Editado Agosto 17, 2022 por Rodrigo R1

[Alan Pacheco]

@Rodrigo R1 fala nobre, eu também tentei desse jeito e vou te mostrar, eu mudei inumeras vezes as medidas inclusive alterando a da tabela de estoque tanto para contagem semana, quanto para semana do ano e ano+semana do ano, abaixo segue o exemplo.
Ele continua apenas fazendo o calculo da (quantidade - consumo)  e não considera o resultado anterior.

Vou inclusive te mostrar meu modelo que é bem simples.
 

Editado Agosto 17, 2022 por Alan Pacheco

[Rodrigo R1]

Alan,

baixei o outro excel que vc enviou e vi que vc fez alguns ajustes acrescentando alguns dados.

Por essa imagem eu entendi que na sua tabela de estoque do seu modelo existem semanas com o valor em blank??? É isso mesmo?

Se for isso mesmo, não existe a necessidade de ter uma linha numa tabela fato onde não houveram registros, seja compra ou venda, etc.

Por que se isso ocorrer, a medida que foi criada, ela irá considerar a última linha com dados e irá fazer as medidas a partir dela.

Lembrando que mesmo blank, ele é considerado como valor para a medida buscar a máxima data do contexto.

Nesse exemplo aí, a máxima data do contexto é a semana 11 e o seu valor será zero - blank.

Por isso a medida que te passei está dando a impressão de estar fazendo apenas a conta de compras - consumo. Mas na verdade ela está fazendo a primeira parte, só que o resultado é: zero(blank) + compras - consumo.

Portanto, a minha sugestão é que você retira as linhas em branco da sua tabela fato de estoque, pois não são necessárias.

Gentileza realizar mais esse teste que deve resolver.

[Rodrigo R1]

Alan....

dá pra resolver isso tbm sem a necessidade de mexer no seu modelo.

Fiz o teste aqui e aproveitei para ajustar a medida com variáveis pra ficar mais elegante...rsrsrs

Estoque Final R1 =

var maiordata = max(dcalendario[Date])

 

var ultimolancamento = CALCULATE(Max(Estoque[Semana]),

                       dcalendario[Date] < maiordata,

                        Estoque[Estoque] <> BLANK())

 

var ultimoestoque = CALCULATE([Resultado R1],

dcalendario[Date] = ultimolancamento)

 

var acumulado = CALCULATE([Compra R1] - [Cons Medio R1],

dcalendario[Date] <= MAX(dcalendario[Date]) &&

dcalendario[Date] > ultimolancamento)

return

 

if([Estoque Inicial R1] = BLANK(),

ultimoestoque + acumulado, [Resultado R1])

Basta você colocar a condição de estoque diferente de blank na variavel ultimolancamento.

Agora num vai dar essa diferença mais, amigo....rsrsrs

Sucesso aí...

[Alan Pacheco]

@Rodrigo R1 mestre a tabela Fato não tem nenhuma linha em branco, o sentido do calculo é poder projetar para frente, o exemplo que dei é porque de fato hoje atualemente estamos na semana 33 seguindo o calendario ISO,  e o que eu quero de Fato é projetar os valores acima de 33 como eu dei no exemplo, se você verificar bem devagar o excel, vai notar que o estoque vai ficar zerado  já a compra você tem dados para frente e o consumo das ultimas 5 semanas também.
espero que eu esteja sendo claro.
Diante mão já te agradeço d+ pela ajuda, eu tb to tentando aqui do outro lado, pois já é um problema que como te falei to a quase 3 semanas.

abaixo segue o excel e o print novamente.
Se verificar de fato o calculo é projetando para frente igual ao do excel, pois é como se a coluna Resolução pasasse a ser o meu estoque então ficaria conforme a segunda imagem que irei alocar com os valores pintados de verde.
Por isso citei acima até o blogue que vi com o laço de repetição, pois já tentei de diversas maneiras e até agora sem sucesso. 😞

DaxLoop.xlsx

Editado Agosto 18, 2022 por Alan Pacheco

[Rodrigo R1]

Alan, estou lendo as suas considerações e juro que não estou conseguindo entender a sua necessidade. Ainda mais que você descreveu seu problema, como é o seu modelo de dados qual a sua necessidade e o resultado esperado.

Utilizei a base de dados que vc disponilizou e encontrei exatamente o resultado que vc apontou acima, destacado em amarelo e verde.

O fato de usar dia, semana ou mês num faz diferença pra lógica a ser aplicada em Dax.

Se vc perceber tanto na imagem e no PBIX que eu mandei, só existe estoque até o mês 06 - que poderia ser a semana 6 ou a 33, tanto faz.

Para os demais itens de compra e consumo, existem dados com datas posterior ao estoque.

Aí vc disse que queria uma projeção do estoque com base nos demais itens.

E veja que fiz exatamente isso, seguindo as regras do negócio, conforme vc informou.

Como no exemplo que eu enviei, o estoque só vai até o mês 06, ele foi projetado até dezembro, que é o último mês do modelo. E do mês 06 ao 12, a medida seguiu a regra do negócio - tanto que apresentou exatamente o resultado espero pelo seu arquivo Excel.

Enfim...sua dúvida é muito boa pra praticar conceitos de Dax e eu acreditei que tinha chegado no resultado que vc esperava - já que o comportamento da medida cumpriu esse objetivo.

Apesar de que, mesmo sendo base fictícia semelhante ao modelo original, não é a mesma coisa ao usar a base oficial. Mas como são dados sensíveis, tbm é compreensível que não podem ser compartilhados. 

Desejo fortemente que vc encontre a solução o quanto antes, amigo.

Sucesso aí....

[Alan Pacheco]

Fala Rodrigão, cara apliquei o mesmo modelo e olha como ficou, detalhe tive que usar relacionamento de muitos para muitos pois fiz a relação pela semana e usando suas mesmas medidas o resultado da errado.

Se você olhar o PBIX seguindo o meu exemplo o seu valor ainda da errado, então creio que não é só a questão do meu modelo e sim da propria medida em si, pois ela ainda não entende que eu devo pegar o valor 15101 e somar com 11957 e diminuir por 6630 e o resultado deve ser calculado para o estoque pra frente.

Resolucao projecao.pbix

[Alan Pacheco]

@Rodrigo R1 fala mestre, cara para você ver que o que eu to falando tem total sentido, troquei a sua base por dados de semana e olha o que aconteceu, de fato é o modelo como você construiu o raciocinio, pois o seu raciocinio não engloba semana.

No seu PBIX fica bem clro que existe um erro quando o calculo é passado a nível de semana, então não é a minha estrutura de dados na base real, ressalto que o que eu apresentei de fato é o exemplo na realidade que estou atualmente.

 

1312079881_DvidaforumAlanPacheco vs Rodrigo R1.pbix

[Rodrigo R1]

Bom dia, @Alan Pacheco. Tudo bem amigo?

Você tem razão, temos que levar em consideração a semana sim.

A medida precisa ser ajustada pra isso.

Hoje a noite eu tento encontrar a solução e te retorno qdo conseguir. 

[Alan Pacheco]

Fala @Rodrigo R1,  cara infelizmente ainda não deu certo, de fato eu dei um exemplo errado a principio, por isso acredito que a glr deve ter tido dificuldade de fazer, pois o meu modelo é um modelo estrela no caso real, onde trago apenas as tabelas dMateriais e dCalendario, seguida de 3 fatos a fCompras, fConsumo e fEstoque.
Segui o seu conselho e adaptei o excel para um modelo real, porém quando aloquei os dados na sua medida os mesmo ficaram bem diferentes repetindo o total geral de tudo.
Agora irei anexar o modelo que eu aloquei uma parcela dos dados reais que eu utilizo de fato, porém seguindo o seu modelo.

No mais fica o meu agradecimento e peço desculpas por ter demorado a postar um modelo real igual aos dados que eu utilizo de fato.

 

Muito obrigado a você pela tentativo, se ainda tiver tempo e puder continuar ajudando fico grato d+ em contra partida continuarei tentando para conseguir achar a solução.

92451931_SoluoAlanForum - ERRO.pbix DaxLoopv2.xlsx

[Rodrigo R1]

Vai dar errado mesmo, @Alan Pacheco....

Cadê o relacionamento da fcompras com a dcalendário??????? kakakaak.....

 

Como eu te falei antes, garante a integridade dos dados o relacionamento entre as tabelas, seguindo as boas práticas que você vai conseguir chegar no resultado...rsrsrs

Tenta aí novamente....kakaka

[Alan Pacheco]

Boa Rodrigo, eu devo ter tirado, mas mesmo com o relacionamento ativo o resultado ainda fica errado.

Agora com os 3 relacionamentos ativos, eu tava tentando até mexer na sua medida. 
Peço desculpas novamente por não ter feito a relação. no exemplo.

Sei que ta tentando ajudar e muito, as vezes eu acabo esquecendo alguma coisa no processo, pelo fato de estar muito focado em tentar resolver, mas mesmo quando ajustei o resultado não mudou muito.

Eu vou deixar a postagem aqui e aguardar e continuar tentando, em paralelo vou tentar falar com a T.I da minha empresa para ver se consigo via python puxar os dados do banco e fazer um looping lá, só vai ser ruim a demora que isso vai levar, mas não desistirei e tentarei ainda no dax em paralelo.

Inclusive totentando resolver aqui quebrando a cabeça

 

92451931_SoluoAlanForum - ERRO.pbix

[Alan Pacheco]

Em 20/08/2022 em 18:23, Rodrigo R1 disse:

Boa tarde @Alan Pacheco. Tudo bem amigo?

Aproveitei agora a tarde para revisar a sua dúvida e postar uma solução definitiva para o seu case.

Peguei o arquivo Excel que você enviou e criei o seguinte modelo:

Veja que é um modelo simples, star schema, com relacionamento de 1 : * onde dimensão filtra fato - seguindo as boas práticas.

Aproveitei e usei um modelo de dcalendario que o Leo disponibilizou na Live 52 - ela é super completa - eu a utilizo sempre em meus modelos e recomendo utiliza-la.

Perceba que a coluna de datas das 3 tabelas fato estão no formato data dd/mm/aaaa - isso para garantir a integridade dos relacionamento, visto que a granularidade da dcalendario é nesse mesmo formato de data também.

Essa é uma etapa de extrema importante, pois se você deixar as tabelas fato no formato de semana e depois criar o relacionamento entre a dcalendario e as tabelas fatos, usando a coluna de semana, irá criar relacionamentos muitos para muitos (* : *) e isso poderá não trazer os resultados esperados.

Após realizar todas as etapas acima, vamos para as medidas:
 

Compras = sum(fCompra[Compra])

Consumo = sum('fCons  Med'[Consumo Méd Ultimas 5 semanas])

Estoque = Sum(fEstoque[Estoque])

Resultado = [Estoque] + [Compras] - [Consumo]
 

Estoque Final R1 =

var vmaxcompra = MAX(fCompra[Data])

var vmaxconsumo = Max('fCons  Med'[Data])

var vmaiordata = Max(vmaxcompra, vmaxconsumo)

var vdatamax = EOMONTH(vmaiordata,0)

 

var vultimoregistroestoque = CALCULATE(max(fEstoque[Data]),

                        dCalendario[Data] <= vdatamax)

 

var vresultado = CALCULATE([Resultado],

                            dCalendario[Data] = vultimoregistroestoque)

 

var vacumulado = CALCULATE(

                    [Compras] - [Consumo],

                    dCalendario[Data] <= vdatamax &&

                    dCalendario[Data] > vultimoregistroestoque)

return

if([Estoque] = BLANK(), vresultado + vacumulado, vresultado)

Jogando os dado em uma tabela, teremos seguinte resultado:

E veja que independente do formato de semana a ser apresentado, ou até mesmo demonstrar por mês, o resultado é o mesmo.

Por fim, a minha sugestão é você revisar o modelo de dados, pois ele pode não estar seguindo as boas práticas e com isso a medida acima pode não apresentar o comportamento desejado.

Segue o PBIX em anexo.

Espero que tenha conseguido ajudar novamente e que você possa encontrar sua solução, pois sei que você está buscando isso há alguns dias.

Sucesso aí, amigo....

Solução Alan Forum.pbix 108 kB · 1 download

Rodrigo, conforme você me ajudou nas mensagens privas, chegamos a um resultado desejado e daqui em diante consigo seguir. Muito obrigado.