As médias são muito úteis em praticamente qualquer aspecto analítico, por estabelecer parâmetros reais de comparação, em lugar de parâmetros abstratos que não correspondem à realidade.
Elas podem ser utilizadas como base para o cálculo e estabelecimento de metas e orçamentos.
No que concerne à indicadores, é comum compararmos o valor corrente, atual, com a média do último trimestre, ou do último semestre ou dos últimos 12 meses, ou seja, estabelece-se um período de tempo como parâmetro (trimestre, semestre ou ano), e então faz-se a comparação do valor atual, que representa o indicador, visando-se encontrar o percentual de variação deste em relação àquele.
Partindo, neste exemplo, de uma situação real onde foi solicitada a comparação dos fatos relativos ao mês corrente com a média do trimestre anterior (sem que o mês corrente fosse considerado no cálculo da mesma), construiremos os cálculos e as comparações no Power BI, mas o mesmo exemplo pode ser executado no Excel, utilizando o Power Pivot.
A base de dados aqui utilizada contém dados de 2014 até junho de 2019 (parcial), registrados diariamente, com mais de um registro por data e com lacunas nas relativas aos finais de semana. Ou seja, a linha do tempo da tabela fato não é linear, nem íntegra, pois as datas não são contíguas.
Para corrigir isto, a tabela fato está relacionada à uma tabela calendário padrão, completa.
O indicador principal, que é o volume atual, já está calculado através de uma soma simples, utilizando a função SUM:
O passo seguinte é calcular a média mensal, o que pode ser feito de diversas formas. Neste exemplo será usada a seguinte fórmula para o cálculo, uma vez que os fatos estão registrados por dia, o que impossibilita o cálculo direto apenas com a função AVERAGE.
Neste cálculo a função VALUES está fornecendo à função AVERAGEX uma tabela dos valores únicos da coluna “Ano/Mês” da tabela Calendário, permitindo a condensação da média do Volume por este critério, e não pelo dia, como está originalmente registrado na tabela fato. A AVERAGEX, por sua vez, altera a iteração do cálculo, fazendo a leitura linha a linha da tabela fato, observando e agregando pelo parâmetro dado pela VALUES.
O passo seguinte é o cálculo da média do último trimestre, desprezando o mês corrente, que por estar incompleto, obviamente reduzirá este número, distorcendo a variação e deturpando a correta interpretação da informação.
Para isto, é necessário primeiro descobrir qual é o último dia do mês anterior. Isto é possível com o uso da função EOMONTH, usando a função TODAY como argumento:
A função TODAY, que retorna a data de hoje, indica a Data Inicial que a função EOMONTH deve usar como parâmetro, e no segundo argumento, o número negativo “-1” indica que ela deve retroceder um mês em relação à data informada no primeiro parâmetro. É simples perceber que este argumento pode ser manipulado para devolver a informação de forma muito elástica e dinâmica.
Uma vez havendo o cálculo da Média Mensal, e sabendo-se de forma automática e dinâmica o último dia do mês anterior ao corrente, é possível calcular a Média do Trimestre Anterior aninhando estes cálculos em uma nova fórmula:
A função CALCULATE permite o recálculo, a mudança de contexto do cálculo original da Média Mensal e a função DATESINPERIOD permite que se escolha o período que representará este novo contexto temporal.
Como primeiro argumento da função CALCULATE recomenda-se utilizar a medida já criada anteriormente para a Média Mensal:
A função DATESINPERIOD fará o papel de filtro dentro da função CALCULATE, alterando o cálculo original. Como argumentos da função DATESINPERIOD, deve-se utilizar o campo Data da tabela Calendário, para indicar a linha de tempo:
O segundo argumento da função DATESINPERIOD é a Data Inicial dentro desta linha do tempo indicada no primeiro argumento. Neste argumento recomenda-se utilizar o cálculo do Último Dia do Mês Anterior, feito anteriormente com a função EOMONTH – ou realizá-lo diretamente, em caso de não ser necessário para outra aplicação:
Ou
O terceiro argumento da função DATESINPERIOD é o número de intervalos de tempo que se deseja avançar ou retroceder de acordo com o contexto do problema. Neste caso, a intenção é retroceder um trimestre, portanto, indica-se isto com um número negativo: -1.
O quarto e último argumento da função DATESINPERIOD é o intervalo propriamente dito, ou seja, em qual granularidade deseja-se tomar o tempo: dias, meses, trimestres ou anos:
Desta forma, finaliza-se o cálculo, encontrando-se por fim, o parâmetro de comparação desejado para o indicador:
O KPI (Key Performance Indicator), deste caso, que é a variação percentual entre o Volume Atual, ou seja do mês corrente, em relação à Média do Último Trimestre, pode ser calculado de forma simples e eficiente com a função DIVIDE:
À esta altura já ficou claro que basta alterar o terceiro argumento da função DATESINPERIOD, que indica o quanto deve-se retroceder ou avançar na linha do tempo, para se encontrar outros parâmetros, como a média do último ano ou do último semestre, por exemplo.
A última imagem pretende resumir o exercício proposto:
