Análise de Tempo no Power Pivot no Excel

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

O DAX (Data Analysis Expressions) 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 análise de tempo não têm nada semelhante no Excel. Isto deve-se ao facto de as funções de análise de tempo trabalharem com dados em constante mudança, dependendo do contexto que selecionar em Tabelas Dinâmicas e visualizações do Power View.

Para trabalhar com funções de análise de tempo, é necessário ter uma tabela de data incluída no seu Modelo de Dados. A tabela de datas tem de incluir uma coluna com uma linha para cada dia de cada ano incluído nos seus dados. Esta coluna é considerada a coluna Data (embora possa ter o nome que pretender). Muitas funções de análise de tempo necessitam da coluna de data para calcular de acordo com as datas que seleciona como campos num relatório. Por exemplo, se tiver uma medida que calcula o saldo de fim de trimestre de fecho utilizando a função CLOSINGBALANCEQTR, para que o Power Pivot saiba quando é realmente o final do trimestre, tem de referenciar a coluna de data na tabela de datas para saber quando o trimestre começa e termina. Para saber mais sobre tabelas de data, consulte o artigo Compreender e criar tabelas de data no Power Pivot no Excel.

Funções

Funções que devolvem uma única data

As funções nesta categoria devolvem uma única data. O resultado pode então ser utilizado como argumentos para outras funções.

As duas primeiras funções nesta categoria devolvem a primeira ou a última data no Date_Column no contexto atual. Isto pode ser útil quando pretende encontrar a primeira ou a última data em que realizou uma transação de um tipo específico. Estas funções utilizam apenas um argumento, o nome da coluna de data na tabela de datas.

As duas funções seguintes nesta categoria localizam a primeira ou última data (ou qualquer outro valor de coluna) onde uma expressão tem um valor que não está em branco. Isto é utilizado com mais frequência em situações como inventário, em que pretende obter o último montante de inventário e não sabe quando o último inventário foi feito.

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

Funções que devolvem um índice de datas

Existem dezasseis funções de análise de tempo que devolvem uma tabela de datas. Na maioria das vezes, estas funções serão utilizadas como um argumento DefinirFiltro para a função CALCULAR . Tal como todas as funções de análise de tempo no DAX, cada função utiliza uma coluna de data como um dos seus argumentos.

As primeiras oito funções nesta categoria começam com uma coluna de data num contexto atual. Por exemplo, se estiver a utilizar uma medida numa tabela dinâmica, poderá existir um mês ou ano nas etiquetas de coluna ou etiquetas de linha. O efeito líquido é que a coluna de data é filtrada para incluir apenas as datas do contexto atual. A partir desse contexto atual, estas oito funções calculam o dia, mês, trimestre ou ano anterior (ou seguinte) e devolvem essas datas na forma de uma tabela de coluna única. As funções "anterior" funcionam para trás a partir da primeira data no contexto atual e as funções "seguinte" avançam a partir da última data no contexto atual.

As quatro funções seguintes nesta categoria são semelhantes, mas em vez de calcularem um período anterior (ou seguinte), calculam o conjunto de datas no período que seja "mês até à data" (ou trimestre até à data, ou ano até à data, ou no mesmo período do ano anterior). Todas estas funções efetuam os seus cálculos com a data mais recente no contexto atual. Tenha em atenção que SAMEPERÍODOLASTYEAR 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 devolverá um erro.

As últimas quatro funções nesta categoria são um pouco mais complexas e também um pouco mais poderosas. Estas funções são utilizadas para mudar do conjunto de datas no contexto atual para um novo conjunto de datas.

  • SOMDATA (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 fim especificada. As restantes três funções deslocam algum número de intervalos de tempo do contexto atual. O intervalo pode ser dia, mês, trimestre ou ano. Estas funções facilitam a mudança do intervalo de tempo de um cálculo através de qualquer um dos seguintes métodos:

  • 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 mudar. Um intervalo positivo avança no tempo, enquanto um intervalo negativo retrocede no tempo. O intervalo em si é especificado por uma palavra-chave de DIA, MÊS, TRIMESTRE ou ANO. Estas palavras-chave não são cadeias de caracteres, por isso não devem estar entre aspas.

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

Esta categoria de funções avalia uma expressão ao longo de um período de tempo especificado. Pode fazer a mesma coisa com a função CALCULAR e outras funções de análise de tempo. Por exemplo:

= TOTALMTD (expressão, Date_Column [, definirFiltro])

é precisamente o mesmo que:

= CALCULATE (expressão, 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 (expressão, Date_Column [, definirFiltro])
  • TOTALQTD (expressão, Date_Column [, definirFiltro])
  • TOTALYTD (expressão, Date_Column [, definirFiltro] [,YE_Date]) *

Também nesta categoria estão um grupo de funções que calculam saldos de abertura e de fecho. Existem certos conceitos que deverá compreender com estas funções específicas. Primeiro, como você pode pensar óbvio, o saldo de abertura de qualquer período é o mesmo que o saldo de fechamento do período anterior. O saldo de fecho inclui todos os dados até ao final do período, enquanto o saldo de abertura não inclui quaisquer dados do período atual.

Estas funções devolvem sempre 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 num período de 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 data mais recente no contexto de data atual.

Recursos adicionais

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

Referência: Referência de Funções DAX no Office.com

Exemplos: modelagem e análise de dados de lucro e prejuízo com o Microsoft PowerPivot no Excel