Inteligência de dados temporais no Power Pivot no Excel

Aplica-se a
Excel para Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

O Data Analysis Expressions (DAX) tem 35 funções especificamente para agregar e comparar dados ao longo do tempo. Ao contrário das funções de data e hora do DAX, as funções de inteligência de tempo não têm nada semelhante no Excel. Isso ocorre porque as funções de inteligência de dados temporais trabalham com dados que estão em constante mudança, dependendo do contexto selecionado nas visualizações de Tabelas Dinâmicas e Power View.

Para trabalhar com funções de inteligência de tempo, você precisa ter uma tabela de data incluída em seu Modelo de Dados. A tabela de datas deve incluir uma coluna com uma linha para cada dia de cada ano incluído em seus dados. Essa coluna é considerada a coluna Data (embora possa ser nomeada como você quiser). Muitas funções de inteligência de dados temporais exigem a coluna data para calcular de acordo com as datas selecionadas como campos em um relatório. Por exemplo, se você tiver uma medida que calcula um saldo final de trimestre de fechamento usando a função CLOSINGBALANCEQTR, para que o Power Pivot saiba quando realmente é o final do trimestre, ele deve fazer referência à coluna de data na tabela de datas para saber quando o trimestre começa e termina. Para saber mais sobre tabelas de data, confira Entender e criar tabelas de data no Power Pivot do Excel.

Funções

Funções que retornam uma única data

As funções nessa categoria retornam uma única data. O resultado pode então ser usado como argumento para outras funções.

As duas primeiras funções nesta categoria retornam a primeira ou a última data no Date_Column no contexto atual. Isso pode ser útil quando você deseja localizar a primeira ou a última data em que teve uma transação de um tipo específico. Essas funções usam apenas um argumento, o nome da coluna de data na tabela de data.

As próximas duas funções nesta categoria localizam a primeira ou a última data (ou qualquer outro valor de coluna também) onde uma expressão tem um valor não vazio. Isso é usado com mais frequência em situações como inventário, em que você deseja obter o último valor de estoque e não sabe quando o último inventário foi feito.

Mais seis funções que retornam uma única data são as funções que retornam a primeira ou a última data de um mês, trimestre ou ano no contexto atual do cálculo.

Funções que retornam uma tabela de datas

Há dezesseis funções de inteligência de dados temporais que retornam uma tabela de datas. Na maioria das vezes, essas funções serão usadas como um argumento SetFilter para a função CALCULATE . Assim como todas as funções de inteligência de tempo no DAX, cada função usa uma coluna de data como um de seus argumentos.

As primeiras oito funções nesta categoria começam com uma coluna de data em um contexto atual. Por exemplo, se estiver usando uma medida em uma Tabela Dinâmica, pode haver um mês ou ano nos rótulos de coluna ou de linha. O efeito líquido é que a coluna de data é filtrada para incluir apenas as datas para o contexto atual. A partir desse contexto atual, essas oito funções calculam o dia, mês, trimestre ou ano anterior (ou seguinte) e retornam essas datas na forma de uma única tabela de coluna. As funções "anterior" funcionam para trás a partir da primeira data no contexto atual, e as funções "próximas" avançam a partir da última data no contexto atual.

As próximas quatro funções nessa categoria são semelhantes, mas, em vez de calcular um período anterior (ou seguinte), elas calculam o conjunto de datas no período que é "acumulado no mês" (ou acumulado no trimestre, ou acumulado no ano, ou no mesmo período do ano anterior). Todas essas funções executam seus cálculos usando a última data no contexto atual. Observe que SAMEPERIODLASTYEAR requer que o contexto atual contenha um conjunto contíguo de datas. Se o contexto atual não for um conjunto contíguo de datas, SAMEPERIODLASTYEAR retornará um erro.

As últimas quatro funções nessa categoria são um pouco mais complexas e também um pouco mais poderosas. Essas funções são usadas para mudar o conjunto de datas que estão no contexto atual para um novo conjunto de datas.

  • DATEADD (Date_Column, Number_of_Intervals, Intervalo)
  • DATESBETWEEN (Date_Column, Start_Date, End_Date)
  • DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Intervalo)

DATESBETWEEN calcula o conjunto de datas entre a data de início e a data de término especificadas. As três funções restantes deslocam algum número de intervalos de tempo do contexto atual. O intervalo pode ser dia, mês, trimestre ou ano. Essas funções facilitam a mudança do intervalo de tempo de um cálculo por qualquer uma das seguintes opções:

  • Voltar dois anos
  • Voltar um mês
  • Avançar três quartos
  • Voltar 14 dias
  • Avançar 28 dias

Em cada caso, você só precisa especificar qual intervalo e quantos desses intervalos devem ser deslocados. Um intervalo positivo avançará no tempo, enquanto um intervalo negativo retrocederá no tempo. O intervalo em si é especificado por uma palavra-chave de DIA, MÊS, TRIMESTRE ou ANO. Essas palavras-chave não são cadeias de caracteres, portanto, não devem estar entre aspas.

Funções que avaliam expressões durante um período de tempo

Essa categoria de funções avalia uma expressão durante um período de tempo especificado. Você pode fazer a mesma coisa usando CALCULATE e outras funções de inteligência de tempo. Por exemplo,

= TOTALMTD (Expressão, Date_Column [, SetFilter])

é exatamente o mesmo que:

= CALCULATE (Expression, DATESMTD (Date_Column)[, SetFilter])

No entanto, é mais fácil usar essas funções de inteligência de tempo quando elas são adequadas para o problema que precisa ser resolvido:

  • TOTALMTD (Expression, Date_Column [, SetFilter])
  • TOTALQTD (Expressão, Date_Column [, SetFilter])
  • TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date]) *

Também nesta categoria estão um grupo de funções que calculam os saldos de abertura e fechamento. Existem certos conceitos que você deve entender com essas funções específicas. Primeiro, como você pode achar óbvio, o saldo de abertura de qualquer período é o mesmo que o saldo de fechamento do período anterior. O saldo de fechamento inclui todos os dados até o final do período, enquanto o saldo de abertura não inclui nenhum dado do período atual.

Essas funções sempre retornam o valor de uma expressão avaliada para um ponto específico no tempo. O ponto no tempo que nos interessa é sempre o último valor de data possível em um período do calendário. O saldo de abertura é baseado na última data do período anterior, enquanto o saldo de fechamento é baseado na última data do período atual. O período atual é sempre determinado pela última data no contexto de data atual.

Recursos adicionais

Artigos: Entender e criar tabelas de data no Power Pivot no Excel

Referência: referência de função DAX no Office.com

Exemplos: Modelagem e Análise de Dados de Lucros e Perdas com o Microsoft PowerPivot no Excel