As tabelas de datas no Power Pivot são essenciais para navegar e calcular dados ao longo do tempo. Este artigo fornece uma compreensão completa das tabelas de data e como você pode criá-las no Power Pivot. Em particular, este artigo descreve:
- Por que uma tabela de datas é importante para procurar e calcular dados por datas e horas.
- Como usar o Power Pivot para adicionar uma tabela de data ao Modelo de Dados.
- Como criar novas colunas de data, como Ano, Mês e Período em uma tabela de datas.
- Como criar relações entre tabelas de data e tabelas de fatos.
- Como trabalhar com o tempo.
Este artigo destina-se a usuários novos no Power Pivot. No entanto, é importante já ter um bom entendimento da importação de dados, criação de relações e criação de colunas e medidas calculadas.
Este artigo não descreve como usar funções DAX Time-Intelligence em fórmulas de medida. Para obter mais informações sobre como criar medidas com as funções de Inteligência de Dados Temporais do DAX, consulte Inteligência de Dados Temporais no Power Pivot no Excel.
Observação
No Power Pivot, os nomes "medida" e "campo calculado" são sinônimos. Estamos usando a medida de nome ao longo deste artigo. Para obter mais informações, consulte Medidas no Power Pivot.
Sumário
Noções básicas sobre tabelas de data
Quase toda análise de dados envolve navegar e comparar dados em datas e horas. Por exemplo, talvez você queira somar os valores de vendas do último trimestre fiscal e, em seguida, comparar esses totais com outros trimestres, ou talvez queira calcular um saldo de fechamento no final do mês para uma conta. Em cada um desses casos, você está usando datas como uma forma de agrupar e agregar transações de vendas ou saldos de um determinado período de tempo.
Relatório Power View
Uma tabela de datas pode conter muitas representações diferentes de datas e horas. Por exemplo, uma tabela de data geralmente terá colunas como Ano Fiscal, Mês, Trimestre ou Período que você pode selecionar como campos de uma Lista de Campos ao dividir e filtrar seus dados em relatórios de Tabelas Dinâmicas ou Power View.
Power View Lista de campos
Para que colunas de data como Ano, Mês e Trimestre incluam todas as datas dentro de seus respectivos intervalos, a tabela de datas deve ter pelo menos uma coluna com um conjunto contíguo de datas. Ou seja, essa coluna deve ter uma linha para cada dia para cada ano incluído na tabela de datas.
Por exemplo, se os dados que você deseja procurar tiverem datas de 1º de fevereiro de 2010 a 30 de novembro de 2012, e você relatar um ano civil, convém obter uma tabela de datas com pelo menos um intervalo de datas de 1º de janeiro de 2010 a 31 de dezembro de 2012. Todos os anos em sua tabela de data deve conter todos os dias de cada ano. Se você estiver atualizando regularmente seus dados com dados mais recentes, convém estender a data de término em um ou dois anos, para que você não precise atualizar sua tabela de datas com o passar do tempo.
Tabela de datas com um conjunto contíguo de datas
Se você relatar um ano fiscal, poderá criar uma tabela de datas com um conjunto contíguo de datas para cada ano fiscal. Por exemplo, se o seu ano fiscal começa em 1º de março e você tem dados dos anos fiscais de 2010 até a data atual (por exemplo, no ano fiscal de 2013), você pode criar uma tabela de datas que começa em 1/3/2009 e inclui pelo menos todos os dias de cada ano fiscal até a última data do ano fiscal de 2013.
Se você relatar o ano civil e o ano fiscal, não precisará criar tabelas de data separadas. Uma única tabela de data pode incluir colunas para um ano civil, ano fiscal e até mesmo um calendário de período de treze e quatro semanas. O importante é que sua tabela de datas contenha um conjunto contíguo de datas para todos os anos incluídos.
Adicionando uma tabela de data ao Modelo de Dados
Existem várias maneiras de adicionar uma tabela de data ao seu Modelo de Dados:
- Importe de um banco de dados relacional ou de outra fonte de dados.
- Crie uma tabela de datas no Excel e copie ou vincule a uma nova tabela no Power Pivot.
- Importar do Microsoft Azure Marketplace.
Vejamos cada um deles mais de perto.
Importar de um banco de dados relacional
Se você importar alguns ou todos os dados de um data warehouse ou outro tipo de banco de dados relacional, é provável que já haja uma tabela de datas e relações entre ela e o restante dos dados que você está importando. As datas e o formato provavelmente corresponderão às datas em seus dados de fato, e as datas provavelmente começam bem no passado e vão muito longe no futuro. A tabela de datas que você deseja importar pode ser muito grande e conter um intervalo de datas além do que você precisará incluir em seu Modelo de Dados. Você pode usar os recursos avançados de filtro do Assistente de Importação de Tabela do Power Pivot para escolher seletivamente apenas as datas e as colunas específicas de que você realmente precisa. Isso pode reduzir significativamente o tamanho da pasta de trabalho e melhorar o desempenho.
Assistente de Importação de Tabela
Na maioria dos casos, você não precisará criar colunas adicionais, como Ano Fiscal, Semana, Nome do Mês etc., porque elas já existirão na tabela importada. No entanto, em alguns casos, depois de importar a tabela de data para o Modelo de Dados, talvez seja necessário criar colunas de data adicionais, dependendo de uma necessidade específica de relatório. Felizmente, isso é fácil de fazer usando o DAX. Você aprenderá mais sobre como criar campos de tabela de data posteriormente. Cada ambiente é diferente. Se você não tiver certeza se suas fontes de dados têm uma tabela de calendário ou data relacionada, fale com o administrador de banco de dados.
Criar uma tabela de datas no Excel
Você pode criar uma tabela de data no Excel e copiá-la para uma nova tabela no Modelo de Dados. Isso é realmente muito fácil de fazer e oferece muita flexibilidade.
Ao criar uma tabela de datas no Excel, você começa com uma única coluna com um intervalo contíguo de datas. Em seguida, você pode criar colunas adicionais, como Ano, Trimestre, Mês, Ano Fiscal, Período, etc., na planilha do Excel usando fórmulas do Excel ou, após copiar a tabela para o Modelo de Dados, crie-as como colunas calculadas. A criação de colunas de data adicionais no Power Pivot é descrita na seção Adicionando Novas Colunas de Data à Tabela de Data mais adiante neste artigo.
Como criar uma tabela de data no Excel e copiá-la para o Modelo de Dados
No Excel, em uma planilha em branco, na célula A1, digite um nome de cabeçalho de coluna para identificar um intervalo de datas. Normalmente, isso será algo como Data, DateTime ou DateKey.
Na célula A2, digite uma data de início. Por exemplo, 01/01/2010.
Clique na alça de preenchimento e arraste-a para baixo até um número de linha que inclua uma data de término. Por exemplo, 31/12/2016.
Selecione todas as linhas na coluna Data (incluindo o nome do cabeçalho na célula A1).
No grupo Estilos , clique em Formatar como Tabela e selecione um estilo.
Na caixa de diálogo Formatar como Tabela, clique em OK.
Copiar todas as linhas, incluindo o cabeçalho.
No Power Pivot, na guia Página Inicial , clique em Colar.
Em Colar Nomeda Tabela de Visualização>, digite um nome, como Data ou Calendário. Deixe a opção Usar a primeira linha como cabeçalhos de colunamarcada e clique em OK.
A nova tabela de datas (chamada Calendar neste exemplo) no Power Pivot tem esta aparência:
Observação
Você também pode criar uma tabela vinculada usando Adicionar ao Modelo de Dados. No entanto, isso torna sua pasta de trabalho desnecessariamente grande porque a pasta de trabalho tem duas versões da tabela de datas; um no Excel e outro no Power Pivot.
Observação
O nome data é uma palavra-chave no Power Pivot. Se você nomear a tabela criada em Data do Power Pivot, será necessário colocar o nome da tabela entre aspas simples em qualquer fórmula DAX que faça referência a ela em um argumento. Todas as imagens e fórmulas de exemplo neste artigo referem-se a uma tabela de datas criada no Power Pivot chamada Calendar.
Agora você tem uma tabela de data em seu Modelo de Dados. Você pode adicionar novas colunas de data, como Ano, Mês etc., usando o DAX.
Adicionando novas colunas de data à tabela de data
Uma tabela de datas com uma única coluna de data que tenha uma linha para cada dia para cada ano é importante para definir todas as datas em um intervalo de datas. Também é necessário para criar uma relação entre a tabela de fatos e a tabela de datas. Mas essa única coluna de data com uma linha para cada dia não é útil ao analisar por datas em um relatório de Tabela Dinâmica ou Power View. Você deseja que sua tabela de datas inclua colunas que o ajudem a agregar seus dados para um intervalo ou grupo de datas. Por exemplo, você pode querer somar os valores de vendas por mês ou trimestre, ou pode criar uma medida que calcula o crescimento ano a ano. Em cada um desses casos, sua tabela de datas precisa de colunas de ano, mês ou trimestre que permitam agregar seus dados para esse período.
Se você importou a tabela de data de uma fonte de dados relacional, ela pode já incluir os diferentes tipos de colunas de data desejados. Em alguns casos, talvez você queira modificar algumas dessas colunas ou criar colunas de data adicionais. Isso é especialmente verdadeiro se você criar sua própria tabela de data no Excel e copiá-la para o Modelo de Dados. Felizmente, criar novas colunas de data no Power Pivot é muito fácil com as funções de data e hora no DAX.
Dica
Se você ainda não trabalhou com o DAX, um ótimo lugar para começar a aprender é com o QuickStart: aprenda o básico do DAX em 30 minutos no Office.com.
Funções de data e hora do DAX
Se você já trabalhou com funções de data e hora em fórmulas do Excel, provavelmente estará familiarizado com as funções de data e hora. Embora essas funções sejam semelhantes às suas contrapartes no Excel, existem algumas diferenças importantes:
- As funções de Data e Hora do DAX usam um tipo de dados de data e hora.
- Eles podem usar valores de uma coluna como argumento.
- Eles podem ser usados para retornar e/ou manipular valores de data.
Essas funções são frequentemente usadas ao criar colunas de data personalizadas em uma tabela de data, portanto, é importante entendê-las. Usaremos várias dessas funções para criar colunas para Ano, Trimestre, Mês Fiscal e assim por diante.
Observação
As funções de Data e Hora no DAX não são iguais às funções de Inteligência de Dados Temporais. Saiba mais sobre a Inteligência de Dados Temporais no Power Pivot no Excel.
O DAX inclui as seguintes funções de Data e Hora:
- DATA
- DATA.VALOR
- DIA SEGUINTE
- EDATE
- EOMONTH
- HOUR
- MINUTE
- MONTH
- AGORA
- SECOND
- HORÁRIO
- VALOR.TEMPO
- HOJE
- WEEKDAY
- WEEKNUM
- YEAR
- YEARFRAC
Há muitas outras funções DAX que você também pode usar em suas fórmulas. Por exemplo, muitas das fórmulas descritas aqui usam funções matemáticas e trigonométricas como MOD e TRUNC, funções lógicas como SE e funções de texto como FORMATO Para obter mais informações sobre outras funções DAX, consulte a seção Recursos adicionais mais adiante neste artigo.
Exemplos de fórmulas para um ano civil
Os exemplos a seguir descrevem fórmulas usadas para criar colunas adicionais em uma tabela de datas chamada Calendar. Uma coluna, chamada Data, já existe e contém um intervalo contíguo de datas de 1/1/2010 a 31/12/2016.
Ano
=ANO([data])
Nessa fórmula, a função ANO retorna o ano do valor na coluna Data. Como o valor na coluna Data é do tipo de dados datetime, a função YEAR sabe como retornar o ano a partir dele.
Mês
=MÊS([data])
Nessa fórmula, assim como na função ANO, podemos simplesmente usar a função MÊS para retornar um valor mensal da coluna Data.
Trimestre
=INT(([Mês]+2)/3)
Nesta fórmula, usamos a função INT para retornar um valor de data como um número inteiro. O argumento que especificamos para a função INT é o valor da coluna Mês, adicione 2 e divida isso por 3 para obter nosso trimestre, de 1 a 4.
Mês Nome
=FORMAT([data];"mmmm")
Nessa fórmula, para obter o nome do mês, usamos a função FORMATAR para converter um valor numérico da coluna Data em texto. Especificamos a coluna Data como o primeiro argumento e, em seguida, o formato; Queremos que o nome do mês mostre todos os caracteres, por isso usamos "MMMM". Nosso resultado fica assim:
Se quisermos retornar o nome do mês abreviado para três letras, usaríamos "mmm" no argumento de formato.
Dia da semana
=FORMAT([data],"ddd")
Nessa fórmula, usamos a função FORMATAR para obter o nome do dia. Como queremos apenas um nome de dia abreviado, especificamos "ddd" no argumento de formato.
Exemplo de Tabela Dinâmica
Depois que tiver campos para datas como Ano, Trimestre, Mês etc., você poderá usá-los em uma Tabela Dinâmica ou em um relatório. Por exemplo, a imagem a seguir mostra o campo SalesAmount da tabela de fatos Sales em VALUES e Year and Quarter da tabela de dimensões Calendar em ROWS. SalesAmount é agregado para contexto de ano e trimestre.
Exemplos de fórmulas para um ano fiscal
Fiscal Year
=SE([Mês]<= 6,[Ano],[Ano]+1)
Neste exemplo, o ano fiscal começa em 1º de julho.
Não há nenhuma função que possa extrair um ano fiscal de um valor de data porque as datas de início e término de um ano fiscal geralmente são diferentes daquelas de um ano civil. Para obter o ano fiscal, primeiro usamos uma função IF para testar se o valor de Month é menor ou igual a 6. No segundo argumento, se o valor de Mês for menor ou igual a 6, retorne o valor da coluna Ano. Caso contrário, retorne o valor do Ano e adicione 1.
Outra maneira de especificar um valor do mês do final do ano fiscal é criar uma medida que simplesmente especifique o mês. Por exemplo, FYE:=6. Em seguida, você pode referenciar o nome da medida no lugar do número do mês. Por exemplo, =SE([Mês]<=[FYE],[Ano],[Ano]+1). Isso fornece mais flexibilidade ao fazer referência ao mês de final do exercício em várias fórmulas diferentes.
Fiscal Month
=SE([Mês]<= 6, 6+[Mês], [Mês]- 6)
Nesta fórmula, especificamos se o valor de [Mês ] for menor ou igual a 6 e, em seguida, pegamos 6 e adicionamos o valor de Mês, caso contrário, subtraímos 6 do valor de [Mês].
Fiscal Quarter
=INT(([FiscalMonth]+2)/3)
A fórmula que usamos para FiscalQuarter é praticamente a mesma que era para Quarter em nosso ano civil. A única diferença é que especificamos [FiscalMonth] em vez de [Month].
Feriados ou datas especiais
Convém incluir uma coluna de data que indique determinadas datas são feriados ou alguma outra data especial. Por exemplo, talvez você queira somar os totais de vendas para o dia de Ano Novo adicionando um campo Feriado a uma Tabela Dinâmica, como uma segmentação de dados ou um filtro. Em outros casos, convém excluir essas datas de outras colunas de data ou em uma medida.
Incluir feriados ou dias especiais é bastante simples. Você pode criar uma tabela no Excel com as datas que deseja incluir. Em seguida, você pode copiar ou usar Adicionar ao Modelo de Dados para adicioná-lo ao Modelo de Dados como uma tabela vinculada. Na maioria dos casos, não é necessário criar uma relação entre a tabela e a tabela Calendar. Todas as fórmulas que fazem referência a ele podem usar a função LOOKUPVALUE para retornar valores.
Abaixo está um exemplo de uma tabela criada no Excel que inclui feriados a serem adicionados à tabela de datas:
| Data | Feriado |
|---|---|
| 1/1/2010 | Ano Novo |
| 11/25/2010 | Ação de Graças |
| 12/25/2010 | Natal |
| 01.01.11 | Ano Novo |
| 11/24/2011 | Ação de Graças |
| 12/25/2011 | Natal |
| 01.01.12 | Ano Novo |
| 22.11.12 | Ação de Graças |
| 12/25/2012 | Natal |
| 1/1/2013 | Ano Novo |
| 11/28/2013 | Ação de Graças |
| 12/25/2013 | Natal |
| 11/27/2014 | Ação de Graças |
| 12/25/2014 | Natal |
| 01/01/2014 | Ano Novo |
| 11/27/2014 | Ação de Graças |
| 12/25/2014 | Natal |
| 1/1/2015 | Ano Novo |
| 11/26/2014 | Ação de Graças |
| 12/25/2015 | Natal |
| 01.01.16 | Ano Novo |
| 11/24/2016 | Ação de Graças |
| 12/25/2016 | Natal |
Na tabela de data, criamos uma coluna chamada Feriado e usamos uma fórmula como esta:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Vejamos essa fórmula com mais cuidado.
Usamos a função LOOKUPVALUE para obter valores da coluna Feriado na tabela Feriados. No primeiro argumento, especificamos a coluna em que estará o valor do resultado. Especificamos a coluna Feriado na tabela Feriados porque esse é o valor que queremos retornar.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Em seguida, especificamos o segundo argumento, a coluna de pesquisa que contém as datas que queremos pesquisar. Especificamos a coluna Data na tabela Feriados , desta forma:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Por fim, especificamos a coluna em nossa tabela Calendar que contém as datas que queremos procurar na tabela Feriado. Obviamente, essa é a coluna Data na tabela Calendar.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
A coluna Feriado retornará o nome do feriado para cada linha que tenha um valor de data que corresponda a uma data na tabela Feriados.
Calendário personalizado - treze períodos de quatro semanas
Algumas organizações, como varejo ou serviços de alimentação, geralmente relatam períodos diferentes, como treze períodos de quatro semanas. Com um calendário de treze períodos de quatro semanas, cada período é de 28 dias; portanto, cada período contém quatro segundas-feiras, quatro terças-feiras, quatro quartas-feiras e assim por diante. Cada período contém o mesmo número de dias e, normalmente, os feriados caem no mesmo período a cada ano. Você pode optar por iniciar um período em qualquer dia da semana. Assim como com as datas em um calendário ou ano fiscal, você pode usar o DAX para criar colunas adicionais com datas personalizadas.
Nos exemplos abaixo, o primeiro período completo começa no primeiro domingo do ano fiscal. Nesse caso, o ano fiscal começa em 01/07.
Semana
Esse valor nos dá o número da semana começando com a primeira semana completa do ano fiscal. Neste exemplo, a primeira semana completa começa no domingo, portanto, a primeira semana completa do primeiro ano fiscal na tabela Calendar começa na verdade em 7/4/2010 e continua até a última semana completa na tabela Calendar. Embora esse valor em si não seja tão útil na análise, é necessário calcular para uso em outras fórmulas de período de 28 dias.
=INT([data]-40356)/7)
Vejamos essa fórmula com mais cuidado.
Primeiro, criamos uma fórmula que retorna valores da coluna Data como um inteiro, assim:
=INT([data])
Em seguida, queremos procurar o primeiro domingo do primeiro ano fiscal. Vemos que é 7/4/2010.
Agora, subtraia 40356 (que é o número inteiro de 27/06/2010, o último domingo do ano fiscal anterior) desse valor para obter o número de dias desde o início dos dias em nossa tabela Calendar, assim:
=INT([data]-40356)
Em seguida, divida o resultado por sete (dias em uma semana), assim:
=INT(([data]-40356)/7)
O resultado fica assim:
Período
O período neste calendário personalizado contém 28 dias e sempre começará em um domingo. Esta coluna retornará o número do período que começa com o primeiro domingo do primeiro ano fiscal.
=INT(([Semana]+3)/4)
Vejamos essa fórmula com mais cuidado.
Primeiro, criamos uma fórmula que retorna um valor da coluna Semana como um número inteiro, da seguinte maneira:
= INT([Semana])
Em seguida, adicione 3 a esse valor, desta forma:
=INT([Semana]+3)
Em seguida, divida o resultado por 4, da seguinte forma:
=INT(([Semana]+3)/4)
O resultado fica assim:
Período Fiscal Year
Esse valor retorna o ano fiscal de um período.
=INT(([Período]+12)/13)+2008
Vejamos essa fórmula com mais cuidado.
Primeiro, criamos uma fórmula que retorna um valor de Period e adiciona 12:
=([Ponto]+12)
Dividimos o resultado por 13, pois há treze períodos de 28 dias no ano fiscal:
=(([Ponto]+12)/13)
Adicionamos 2010, porque esse é o primeiro ano na tabela:
=(([Período]+12)/13)+2010
Por fim, usamos a função INT para remover qualquer fração do resultado e retornar um número inteiro, quando dividido por 13, assim:
= INT(([Período]+12)/13)+2010
O resultado fica assim:
Período em FiscalYear
Esse valor retorna o número do período, 1 – 13, começando com o primeiro Período completo (começando no domingo) em cada ano fiscal.
=SE(MOD([Período];13), MOD([Período];13);13)
Esta fórmula é um pouco mais complexa, então vamos descrevê-la primeiro em um idioma que entendemos melhor. Esta fórmula indica, divida o valor de [Período] por 13 para obter um número de período (1-13) no ano. Se esse número for 0, retorne 13.
Primeiro, criamos uma fórmula que retorna o restante do valor de Period por 13. Podemos usar o MOD (funções matemáticas e trigonométricas) assim:
= MOD([Period],13)
Isso, na maioria das vezes, nos dá o resultado desejado, exceto quando o valor de Period é 0 porque essas datas não caem no primeiro ano fiscal, como nos primeiros cinco dias de nossa tabela de data Calendar de exemplo. Podemos cuidar disso com uma função IF. Caso nosso resultado seja 0, retornamos 13, desta forma:
= SE(MOD([Período],13),MOD([Período],13),13)
O resultado fica assim:
Exemplo de Tabela Dinâmica
A imagem abaixo mostra uma Tabela Dinâmica com o campo SalesAmount da tabela de fatos Vendas em VALORES e os campos PeriodFiscalYear e PeriodInFiscalYear da tabela de dimensões de data do Calendar em LINHAS. SalesAmount é agregado para o contexto por ano fiscal e período de 28 dias no ano fiscal.
Relações
Depois de criar uma tabela de datas no seu Modelo de Dados, para começar a procurar seus dados em Tabelas Dinâmicas e relatórios e agregar dados com base nas colunas da tabela de dimensão de data, você precisa criar um relacionamento entre a tabela de fatos com os dados da transação e a tabela de datas.
Como você precisa criar uma relação baseada em datas, convém certificar-se de criar essa relação entre colunas cujos valores são do tipo de dados datetime (Data).
Para cada valor de data na tabela de fatos, a coluna de pesquisa relacionada na tabela de datas deve conter valores correspondentes. Por exemplo, uma linha (registro de transação) na tabela Fatos de vendas com um valor de 15/8/2012 12:00 na coluna DateKey deve ter um valor correspondente na coluna Data relacionada na tabela data (denominada Calendar). Esse é um dos motivos mais importantes pelos quais você deseja que a coluna de data na tabela de datas contenha um intervalo contíguo de datas que inclua qualquer data possível na tabela de fatos.
Observação
Embora a coluna de data em cada tabela deva ser do mesmo tipo de dados (Data), o formato de cada coluna não importa.
Observação
Se o Power Pivot não permitir que você crie relações entre as duas tabelas, os campos de data podem não armazenar a data e a hora com o mesmo nível de precisão. Dependendo da formatação da coluna, os valores podem ter a mesma aparência, mas podem ser armazenados de forma diferente. Leia mais sobre como trabalhar com tempo.
Observação
Evite usar chaves alternativas inteiras nas relações. Quando você importa dados de uma fonte de dados relacional, geralmente as colunas de data e hora são representadas por uma chave alternativa, que é uma coluna inteira usada para representar uma data exclusiva. No Power Pivot, você deve evitar criar relações usando chaves de data/hora inteiras e, em vez disso, usar colunas que contenham valores exclusivos com um tipo de dados de data. Embora o uso de chaves alternativas seja considerado uma prática recomendada em data warehouses tradicionais, as chaves inteiras não são necessárias no Power Pivot e podem dificultar o agrupamento de valores em Tabelas Dinâmicas por períodos de data diferentes.
Se você receber um erro de incompatibilidade de tipo ao tentar criar uma relação, é provável que a coluna na tabela de fatos não seja do tipo de dados Data. Isso pode acontecer quando o Power Pivot não consegue converter automaticamente um tipo de dados que não é data (geralmente um tipo de dados de texto) em um tipo de dados de data. Você ainda poderá usar a coluna em sua tabela de fatos, mas precisará converter os dados com uma fórmula DAX em uma nova coluna calculada. Consulte Convertendo datas de tipo de dados de texto em um tipo de dados de data posteriormente no apêndice.
Vários relacionamentos
Em alguns casos, pode ser necessário criar várias relações ou criar várias tabelas de datas. Por exemplo, se houver vários campos de data na tabela de fatos de vendas, como DateKey, ShipDate e ReturnDate, todos eles poderão ter relações com o campo Data na tabela de data do Calendar, mas apenas um deles poderá ser uma relação ativa. Nesse caso, como DateKey representa a data da transação e, portanto, a data mais importante, isso serviria melhor como a relação ativa . Os outros têm relacionamentos inativos.
A Tabela Dinâmica a seguir calcula o total de vendas por Ano Fiscal e Trimestre Fiscal. Uma medida denominada Total Sales, com a fórmula Total Sales:=SUM([SalesAmount]), é colocada em VALUES, e os campos FiscalYear e FiscalQuarter da tabela de data do Calendar são colocados em ROWS.
Esta Tabela Dinâmica direta funciona corretamente porque queremos somar nossas vendas totais pela transactiondate em DateKey. Nossa medida de Total de Vendas usa as datas em DateKey e é somada por ano fiscal e trimestre fiscal porque há uma relação entre DateKey na tabela Vendas e a coluna Data na tabela de data do Calendar.
Relações inativas
Mas, e se quiséssemos somar nossas vendas totais não por data de transação, mas por data de envio? Precisamos de uma relação entre a coluna DataEnvio na tabela Vendas e a coluna Data na tabela Calendar. Se não criarmos essa relação, nossas agregações serão sempre baseadas na data da transação. No entanto, podemos ter várias relações, embora apenas uma possa estar ativa e, como a data da transação é a mais importante, ela obtém a relação ativa com a tabela Calendar.
Nesse caso, DataDeEnvio tem uma relação inativa, portanto, qualquer fórmula de medida criada para agregar dados com base em datas de remessa deve especificar a relação inativa usando a função USERELATIONSHIP .
Por exemplo, como há uma relação inativa entre a coluna DataEnvio na tabela Vendas e a coluna Data na tabela Calendar, podemos criar uma medida que soma o total de vendas por data de envio. Usamos uma fórmula como esta para especificar a relação a ser usada:
Total de Vendas por Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Essa fórmula simplesmente indica: Calcule uma soma para SalesAmount, mas filtre usando a relação entre a coluna ShipDate na tabela Sales e a coluna Date na tabela Calendar.
Agora, se criarmos uma Tabela Dinâmica e colocarmos a medida Total de Vendas por Data de Envio em VALORES e o Ano Fiscal e o Trimestre Fiscal em LINHAS, veremos o mesmo Total Geral, mas todos os outros valores de soma para o ano fiscal e o trimestre fiscal são diferentes porque são baseados na data de envio e não na data da transação.
O uso de relações inativas permite que você use apenas uma tabela de data, mas exige que quaisquer medidas (como Total de Vendas por Data de Envio) façam referência à relação inativa em sua fórmula. Existe outra alternativa, ou seja, usar várias tabelas de datas.
Várias tabelas de datas
Outra maneira de trabalhar com várias colunas de data em sua tabela de fatos é criar várias tabelas de data e criar relações ativas separadas entre elas. Vejamos nosso exemplo de tabela de vendas novamente. Temos três colunas com datas sobre as quais talvez desejemos agregar dados:
- Uma DateKey com a data de venda para cada transação.
- Uma ShipDate – com a data e hora em que os itens vendidos foram enviados ao cliente.
- Uma ReturnDate – com a data e hora em que um ou mais itens devolvidos foram recebidos.
Lembre-se de que o campo DateKey com a data da transação é o mais importante. Faremos a maioria de nossas agregações com base nessas datas, portanto, certamente desejaremos uma relação entre elas e a coluna Data na tabela Calendar. Se não quisermos criar relações inativas entre ShipDate e ReturnDate e o campo Date na tabela Calendar, exigindo fórmulas de medidas especiais, podemos criar tabelas de data adicionais para data de envio e data de retorno. Podemos então criar relações ativas entre eles.
Neste exemplo, criamos outra tabela de datas chamada ShipCalendar. Isso, é claro, também significa criar colunas de data adicionais e, como essas colunas de data estão em uma tabela de data diferente, queremos nomeá-las de maneira a diferenciá-las das mesmas colunas na tabela Calendar. Por exemplo, criamos colunas chamadas ShipYear, ShipMonth, ShipQuarter e assim por diante.
Se criarmos nossa Tabela Dinâmica e colocarmos nossa medida Total de Vendas em VALORES, e ShipFiscalYear e ShipFiscalQuarter em LINHAS, veremos os mesmos resultados que vimos quando criamos uma relação inativa e um campo calculado especial Total de Vendas por Data de Envio.
Cada uma dessas abordagens requer uma consideração cuidadosa. Ao usar várias relações com uma única tabela de data, talvez seja necessário criar medidas especiais que transitam por relações inativas usando a função USERELATIONSHIP. Por outro lado, a criação de várias tabelas de data pode ser confusa em uma Lista de Campos e, como você tem mais tabelas no Modelo de Dados, isso exigirá mais memória. Experimente o que funciona melhor para você.
Data Propriedade da tabela
A propriedade Tabela de Datas define os metadados necessários para que funções Time-Intelligence, como TOTALYTD, PREVIOUSMONTH e DATESBETWEEN funcionem corretamente. Quando um cálculo é executado usando uma dessas funções, o mecanismo de fórmula do Power Pivot sabe onde ir para obter as datas necessárias.
Aviso
Se essa propriedade não estiver definida, as medidas que usam funções DAX Time-Intelligence podem não retornar resultados corretos.
Ao definir a propriedade Tabela de Data, você especifica uma tabela de data e uma coluna de data do tipo de dados Data (datetime) nela.
Como definir a propriedade Tabela de Data
- Na janela do PowerPivot, selecione a tabela Calendar.
- Na guia Design , clique em Marcar como tabela de data.
- Na caixa de diálogo Marcar como Tabela de Data, selecione uma coluna com valores exclusivos e o tipo de dados Data.
Trabalhando com o tempo
Todos os valores de data com um tipo de dados Data no Excel ou no SQL Server são, na verdade, um número. Incluídos nesse número estão os dígitos que se referem a uma hora. Em muitos casos, esse tempo para cada linha é meia-noite. Por exemplo, se um campo DateTimeKey em uma tabela de fatos de vendas tiver valores como 19/10/2010 12:00:00 AM, isso significa que os valores estão no nível de precisão do dia. Se os valores do campo DateTimeKey tiverem uma hora incluída, por exemplo, 19/10/2010 8:44:00, isso significa que os valores estão no nível de precisão de um minuto. Os valores também podem ser de precisão no nível de hora ou até mesmo de segundos. O nível de precisão no valor de tempo terá um impacto significativo em como você cria sua tabela de data e as relações entre ela e sua tabela de fatos.
Você precisa determinar se agregará seus dados para um nível de precisão diário ou para um nível de precisão de tempo. Em outras palavras, convém usar colunas na tabela de data, como Manhã, Tarde ou Hora, como campos de data de hora nas áreas de Linha, Coluna ou Filtro de uma Tabela Dinâmica.
Observação
Os dias são a menor unidade de tempo com a qual as funções de Inteligência de Dados de Tempo do DAX podem trabalhar. Se você não precisa trabalhar com valores de tempo, deve reduzir a precisão de seus dados para usar dias como a unidade mínima.
Se você pretende agregar seus dados ao nível de tempo, sua tabela de data precisará de uma coluna de data com a hora incluída. Na verdade, ele precisará de uma coluna de data com uma linha para cada hora, ou talvez até a cada minuto, de cada dia, para cada ano no intervalo de datas. Isso ocorre porque, para criar uma relação entre a coluna DateTimeKey na tabela de fatos e a coluna de data na tabela de data, você deve ter valores correspondentes. Como você pode imaginar, se você incluir muitos anos, isso pode resultar em uma tabela de data muito grande.
Na maioria dos casos, porém, você deseja agregar seus dados apenas para o dia. Em outras palavras, você usará colunas como Ano, Mês, Semana ou Dia da Semana como campos nas áreas de Linha, Coluna ou Filtro de uma Tabela Dinâmica. Nesse caso, a coluna de data na tabela de data precisa conter apenas uma linha para cada dia em um ano, conforme descrito anteriormente.
Se sua coluna de data incluir um nível de precisão de hora, mas você agregar apenas a um nível de dia, para criar a relação entre a tabela de fatos e a tabela de data, talvez seja necessário modificar sua tabela de fatos criando uma nova coluna que trunca os valores na coluna de data para um valor de dia. Em outras palavras, converta um valor como 19/10/2010 08:44:00 para 19/10/2010 00:00:00. Você pode então criar a relação entre essa nova coluna e a coluna de data na tabela de data porque os valores correspondem.
Vamos ver um exemplo. Esta imagem mostra uma coluna DateTimeKey na tabela de fatos de vendas. Todas as agregações dos dados nesta tabela só precisam ser no nível do dia, usando colunas na tabela de data do Calendar, como Ano, Mês, Trimestre, etc. A hora incluída no valor não é relevante, apenas a data real.
Como não precisamos analisar esses dados no nível de tempo, não precisamos que a coluna Data na tabela de datas do Calendar inclua uma linha para cada hora e cada minuto de cada dia em cada ano. Portanto, a coluna Data em nossa tabela de data tem esta aparência:
Para criar uma relação entre a coluna DateTimeKey na tabela Sales e a coluna Date na tabela Calendar, podemos criar uma nova coluna calculada na tabela de fatos Sales e usar a função TRUNC para truncar o valor de data e hora na coluna DateTimeKey em um valor de data que corresponda aos valores na coluna Date na tabela Calendar. Nossa fórmula tem esta aparência:
=TRUNC([DateTimeKey],0)
Isso nos dá uma nova coluna (chamamos de DateKey) com a data da coluna DateTimeKey e uma hora de 12:00:00 para cada linha:
Agora podemos criar uma relação entre essa nova coluna (DateKey) e a coluna Data na tabela Calendar.
Da mesma forma, podemos criar uma coluna calculada na tabela Sales que reduz a precisão de tempo na coluna DateTimeKey para o nível de precisão hora. Nesse caso, a função TRUNC não funcionará, mas ainda podemos usar outras funções de Data e Hora do DAX para extrair e reconcatenar um novo valor para um nível de precisão de hora. Podemos usar uma fórmula como esta:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Nossa nova coluna tem esta aparência:
Desde que nossa coluna Data na tabela de data tenha valores com precisão no nível de hora, podemos criar uma relação entre elas.
Tornando as datas mais utilizáveis
Muitas das colunas de data que você cria na tabela de data são necessárias para outros campos, mas realmente não são tão úteis na análise. Por exemplo, o campo DateKey na tabela Sales à qual nos referimos e mostramos ao longo deste artigo é importante porque, para cada transação, essa transação é registrada como ocorrendo em uma data e hora específicas. Mas, do ponto de vista da análise e do relatório, não é tão útil porque não podemos usá-lo como um campo de linha, coluna ou filtro em uma Tabela Dinâmica ou relatório.
Da mesma forma, em nosso exemplo, a coluna Data na tabela Calendar é muito útil, crítica de fato, mas você não pode usá-la como uma dimensão em uma Tabela Dinâmica.
Para manter as tabelas e colunas nelas o mais úteis possível e para tornar a Tabela Dinâmica ou o relatório do Power View Listas de campos mais fáceis de navegar, é importante ocultar colunas desnecessárias das ferramentas do cliente. Também é possível ocultar algumas tabelas. A tabela de feriados mostrada anteriormente contém datas de feriados que são importantes para certas colunas na tabela Calendar, mas você não pode usar as colunas Data e Feriados da tabela Feriados como campos em uma Tabela Dinâmica. Aqui, novamente, para facilitar a navegação das Listas de Campos, você pode ocultar toda a tabela de Feriados.
Outro aspecto importante do trabalho com datas são as convenções de nomenclatura. Você pode nomear tabelas e colunas no Power Pivot como quiser. Mas lembre-se de que, especialmente se você for compartilhar sua pasta de trabalho com outros usuários, uma boa convenção de nomenclatura facilita a identificação de tabelas e datas, não apenas em Listas de Campos, mas também no Power Pivot e em fórmulas DAX.
Depois de ter uma tabela de data em seu Modelo de Dados, você pode começar a criar medidas que o ajudarão a tirar o máximo proveito de seus dados. Alguns podem ser tão simples quanto somar os totais de vendas do ano atual, e outros podem ser mais complexos, em que você precisa filtrar em um intervalo específico de datas exclusivas. Saiba mais em Medidas nas funções Power Pivot e Time Intelligence.
Apêndice
Converter datas de tipo de dados de texto em um tipo de dados de data
Em alguns casos, uma tabela de fatos com dados de transação pode conter datas do tipo de dados de texto. Ou seja, uma data que aparece como 2012-12-04T11:47:09 na verdade não é uma data ou, pelo menos, não é o tipo de data que o Power Pivot pode entender. Na verdade, é apenas um texto que parece uma data. Para criar uma relação entre uma coluna de data na tabela de fatos e uma coluna de data em uma tabela de datas, ambas as colunas devem ser do tipo de dados Data .
Normalmente, quando você tenta alterar o tipo de dados de uma coluna de datas que são do tipo de dados de texto para um tipo de dados de data, o Power Pivot pode interpretar as datas e convertê-las automaticamente em um tipo de dados de data verdadeira. Se o Power Pivot não puder fazer uma conversão de tipo de dados, você receberá um erro de incompatibilidade de tipo.
No entanto, você ainda pode converter as datas em um tipo de dados de data verdadeira. Você pode criar uma nova coluna calculada e usar uma fórmula DAX para analisar o ano, mês, dia, hora, etc. das cadeias de caracteres de texto e, em seguida, concatená-las novamente de uma forma que o Power Pivot possa ler como uma data verdadeira.
Neste exemplo, importamos uma tabela de fatos chamada Vendas para o Power Pivot. Ela contém uma coluna chamada DateTime. Os valores são exibidos assim:
Se examinarmos o Tipo de Dados na guia Página Inicial do grupo Formatação do Power Pivot, veremos que é o tipo de dados de Texto.
Não é possível criar uma relação entre a coluna Data/Hora e a coluna Data em nossa tabela de data porque os tipos de dados não correspondem. Se tentarmos alterar o tipo de dados para Data, obteremos um erro de incompatibilidade de tipo:
Nesse caso, o Power Pivot não pôde converter o tipo de dados de texto em data. Ainda podemos usar essa coluna, mas para colocá-la em um tipo de dados de data verdadeira, precisamos criar uma nova coluna que analise o texto e o recrie em um valor que o Power Pivot pode criar um tipo de dados Data.
Lembre-se, da seção Trabalhando com o tempo anteriormente neste artigo; A menos que seja necessário que sua análise seja para um nível de precisão de hora do dia, você deve converter as datas em sua tabela de fatos para um nível de precisão do dia. Com isso em mente, queremos que os valores em nossa nova coluna estejam no nível de precisão do dia (excluindo o tempo). Podemos converter os valores da coluna DateTime em um tipo de dados de data e remover o nível de precisão de tempo com a seguinte fórmula:
=DATA(ESQUERDA([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
Isso nos dá uma nova coluna (nesse caso, chamada Data). O Power Pivot detecta até mesmo os valores como datas e define o tipo de dados automaticamente como Data.
Se quisermos preservar o nível de precisão do tempo, basta estender a fórmula para incluir as horas, os minutos e os segundos.
=DATA(ESQUERDA([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
Agora que temos uma coluna Data do tipo de dados Data, podemos criar uma relação entre ela e uma coluna de data em uma data.
Recursos adicionais
Início rápido: aprenda os fundamentos de DAX em 30 minutos