Redistribuição de Meta

[Daiana]

Pessoal, boa noite!

Estou há uns dias tentando fazer o cálculo de redistribuição de uma meta.

Eu tenho uma meta mensal que já é diluída por dia de acordo com o percentual que é calculado e estipulado pela gerência.

O que eu quero é o seguinte.

Em casos onde a meta diária não seja batida, eu quero que ele pegue a diferença (Realizado vs Meta) e dilua para os dias a realizar.

Em casos onde a meta diária seja ultrapassada, eu quero que pegue a diferença (Realizado vs Meta) e subtraia das próximas metas.

Eu tentei seguir um racicionio onde mostro em "Como eu espero que seja" no Excel, mas não consegui chegar muito longe no Power BI.

 

Book1 (1).xlsx

Editado Novembro 29, 2021 por Daiana

[Bruno Abdalla de Souza]

Veja se faz sentido. Não sei se entendi completamente.

Nova Meta = 
    VAR vDataContexto = 
        MAX(dCalendario[Data])
    VAR vUltimaDataRealizado = 
        CALCULATE(
            MAX(fBase[Date]),
            REMOVEFILTERS(dCalendario[Data])
        )
    VAR vMeta = 
        [Meta Visitas]
    VAR vAcumuladoRealizado = 
        CALCULATE(
            [Acumulado sessoes realizadas],
            dCalendario[Data] = vUltimaDataRealizado
        )
    VAR vAcumuladoMeta = 
        CALCULATE(
            [Meta Visitas acumulado],
            dCalendario[Data] = vUltimaDataRealizado
        )
    VAR vDiasMes = 
        CALCULATE(
            MAX(dCalendario[Data]),
            REMOVEFILTERS(dCalendario[Data])
        )
    VAR vDiasFaltantes = 
        DATEDIFF(vUltimaDataRealizado, vDiasMes, DAY)
    VAR vTaxaMeta = 
        DIVIDE(
            ABS(vAcumuladoMeta - vAcumuladoRealizado),
            vDiasFaltantes
        )
    VAR vDiasFaltantesCorrigido = 
        COUNTROWS(
            FILTER(
                VALUES(dCalendario[Data]),
                dCalendario[Data] > vUltimaDataRealizado &&
                vTaxaMeta >= vMeta
            )
        )
    VAR vTaxaMetaCorrigida = 
        DIVIDE(
            ABS(vAcumuladoMeta - vAcumuladoRealizado),
            vDiasFaltantesCorrigido
        )
    VAR vNovaMeta =
        vMeta +  
        IF(
            vAcumuladoMeta < vAcumuladoRealizado,
            -1,
            1
        ) * vTaxaMetaCorrigida

RETURN
    IF(
        vDataContexto > vUltimaDataRealizado && 
        vMeta >= vTaxaMeta,
        vNovaMeta,
        vMeta
    )

 

Teste.pbix

[Rafa Lemos]

Oi @Daiana,

Tudo bom?

 

Conseguiu resolver seu caso?

 

Se não, tem a [Live #76 Mestre Power BI] Como fazer Forecasting e Rateio de Metas por Dias Úteis - Lives sobre Linguagem DAX - Pilar 4  que pode te ajudar.

[Daiana]

Oi, Rafa!

Agradeço a resposta. Já assisti ao vídeo mas ainda não soube como resolver..

A questão é que eu já tenho essa meta diluída pelos dias. O que eu gostaria é que ela fosse recalculada sempre que ultrapassasse ou não batesse a meta diária. 

Tentei fazer uma forma como está no arquivo Excel, mas não consegui desenvolver muito no Power BI, pois preciso considerar os dois cenários, o que bateu e o que não bateu.. 

[Bruno Abdalla de Souza]

Você já não tem estes dados migrados para o Power BI? Tem tanta coisa lá no arquivo em Excel que fiquei em dúvida o que é a base de dados e o que são os cálculos que vocês para chegar na resposta.
Se puder anexar o pbix e a base de dados, acho que facilita.

[Daiana]

Oi, Bruno!

Segue o PBIx com as tabelas certinhas.

O valor Absoluto e o valor Percentual já vêm prontos pra mim.

Teste.pbix

[Bruno Abdalla de Souza]

Na base de realizado, temos usuários, novos usuários e sessões. Na base de metas, temos custo, visitas, transações e receita. Qual campo do realizado pode ser comparado com qual campo da meta?

Explica um pouco melhor os seus dados, por favor.

[Daiana]

Oi, Bruno! 

Desculpa! 

Estou fazendo somente com o Visitas / Sessões que, no caso, são a mesma coisa. Se eu conseguir com Visitas / Sessões eu replico para os outros.

[Daiana]

Oi, Bruno!

Agradeço muito pela resposta!!

Será que você poderia me explicar a medida? rs 

Eu entendi mais ou menos o que ela está fazendo e testei aqui com alguns valores, mas a Nova Meta permanece estática. Talvez, seja o fato de eu não ter entendido mesmo rs

Mas a ideia é que ela sempre recalcule quando faço a mais ou quando faço a menos

[Bruno Abdalla de Souza]

A regra que fiz foi:

 

1) Calcula a ultima data com visitas. No exemplo que anexei, era 19/11 se não em engano.
2) Calcula o acumulado realizado até 19/11.
3) Calcula o acumulado de meta até 19/11.
4) Calcula a diferença entre meta acumulada e realizado acumulado.
5) Calcula dias faltantes (30/11 - 19/11).
6) Calcula fator de correção (diferença acumulado dividido por dias faltantes).
7) Calcula dias faltantes cuja meta seja maior ou igual ao fator de correção (caso contrário a meta recalculada ficará negativa quando a correção da meta for para baixo).
8 Recalcula fator de correção (diferença acumulado por dias faltantes corrigido) corrigido.
9) Calcula nova meta: Nova Meta = Meta + FatorCorreçãoCorrigido, onde o fator de correção pode ser negativo ou positivo, depende se o realizado acumulado foi maior ou menor que a meta acumulada.

Ou seja, eu não fiquei verificando a meta diária, até porque não entendi essa sua regra. Eu calculei a correção da meta baseado no acumulado até a última data de realização, corrigindo apenas o futuro e mantendo a meta do passado até a última data com visitas.

 

 

[Roseane]

@Daiana bom dia, conseguiu replicar a solução do Bruno após a explicação?

[Daiana]

@Bruno Abdalla de Souza, desculpa, mas eu devo ser muito burra 😓 porque eu entendi mais ou menos o que a fórmula está fazendo pela sua explicação, mas não entendi o porquê os valores futuros estão estáticos. Eu testei aqui modificando os valores do realizado para ficarem menores, mas os valores futuros continuam os mesmos. 

O que eu tinha pensado no Excel foi obter um realizado máximo diariamente pegando acumulado da meta e subtraindo o valor do realizado.

Quando o realizado for maior que o realizado máximo, ele faz o calculo de diferença, divide esse valor para os dias a realizar e subtrai nas próximas metas. Assim, eu estaria sempre dentro dela.

A questão é que, em outros casos, teria que fazer o contrario quando o realizado fosse menor que o realizado máximo. Daí, não soube desenvolver.. 

[Bruno Abdalla de Souza]

Vamos a um exemplo:

Realizado acumulado até 20/11: 10.000.000

Meta acumulada até 20/11: 7.000.000

Diferença entre Acumulado até 20/11: 3.000.000

Dias Faltantes: 10 dias (30/11 - 20/11)

Valor para corrigir a meta diária a realizar = 3.000.000 / 10 = 300.000


Vamos supor que suas metas sejam as seguintes de 21/11 a 30/11:

Data                       Meta

21/11                     350.000

22/11                     400.000

23/11                     110.000

24/11 a 31/11     450.000          

Note que tenho que corrigir cada meta diária em 300.000 para baixo, por o realizado foi maior que a meta. Porém, em 23/11, a meta é menor que a correção (110.000 x 300.000 de correção), o que deixaria a meta negativa (o que não faz sentido). Logo, eu recalculo quantias dias faltantes possuem uma meta maior ou igual à correção. Neste caso, apenas 23/11 não satisfaz a condição. Logo, tenho 9 dias que precisam ser corrigidos, excluindo o dia 23/11.
Assim, recalculo a correção da meta para 9 dias e não amis 10 dias:

Nova Correção da Meta = 3.000.000 / 9 = 333.333

Logo, as novas metas ficariam:

Data                          Meta

21/11                        16.667

22/11                        66.667

23/11                        110.000

24 a 31/11               66.667

 

Ou seja, corrigi todas as datas, menos a que estava mais baixa, que era menor que a correção da meta. No passado (dias anterior ou iguais ao último dia com dados de visitas), a meta seria igual a meta original. Somente nas datas posteriores é que a meta seria corrigida.

Caso você queira fazer correções antes da última data com visitas, aí sugiro fazer isso já no PowerQuery (pelo menos para os dados passados) e não via DAX, pois no DAX acaba ficando mais complexo. Eu geraria uma nova coluna no PowerQuery com a NovaMeta.

 

Espero ter ficado um pouco mais claro qual foi a minha ideia.

 

 

 

 

[Daiana]

Muito obrigada, @Bruno Abdalla de Souza!! Esclareceu demais!!

Agora entendi, rs e já estou aplicando! 

Obrigada mesmo pela paciência!!