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 detalhada das tabelas de datas e como pode criá-las no Power Pivot. Em particular, este artigo descreve:
- Por que motivo uma tabela de datas é importante para navegar e calcular dados por datas e hora.
- Como utilizar o Power Pivot para adicionar uma tabela de datas ao Modelo de Dados.
- Como criar novas colunas de data, como Ano, Mês e Período numa tabela de datas.
- Como criar relações entre tabelas de datas e tabelas de factos.
- Como trabalhar com o tempo.
Este artigo destina-se a utilizadores novos no Power Pivot. No entanto, é importante já ter uma boa compreensão da importação de dados, da criação de relações e da criação de colunas e medidas calculadas.
Este artigo não descreve como utilizar funções de Time-Intelligence DAX em fórmulas de medida. Para obter mais informações sobre como criar medidas com funções de Análise de Tempo DAX, veja Análise de Tempo no Power Pivot no Excel.
Observação
No Power Pivot, os nomes "measure" e "calculated field" são sinónimos. Estamos a utilizar a medida de nome ao longo deste artigo. Para obter mais informações, veja Medidas no Power Pivot.
Sumário
Compreender as tabelas de datas
Quase todas as análises de dados envolvem a navegação e a comparação de dados ao longo de datas e horas. Por exemplo, pode querer somar os montantes de vendas do último trimestre fiscal e, em seguida, comparar esses totais com outros trimestres ou pode querer calcular um saldo final de mês para uma conta. Em cada um destes casos, está a utilizar datas como forma de agrupar e agregar transações ou saldos de vendas durante um determinado período de tempo.
Relatório do Power View
Uma tabela de datas pode conter muitas representações diferentes de datas e hora. Por exemplo, uma tabela de datas terá frequentemente colunas como Ano Fiscal, Mês, Trimestre ou Período que pode selecionar como campos de uma Lista de Campos ao cortar e filtrar os seus dados em tabelas dinâmicas ou relatórios do Power View.
Lista de Campos do Power View
Para que colunas de data como Ano, Mês e Trimestre incluam todas as datas dentro do respetivo intervalo, a tabela de datas tem de ter, pelo menos, uma coluna com um conjunto contíguo de datas. Ou seja, essa coluna tem de ter uma linha para cada dia para cada ano incluída na tabela de datas.
Por exemplo, se os dados que pretende procurar tiverem datas de 1 de fevereiro de 2010 a 30 de novembro de 2012 e reportar um ano de calendário, irá querer uma tabela de datas com, pelo menos, um intervalo de datas entre 1 de janeiro de 2010 e 31 de dezembro de 2012. Todos os anos na tabela de datas tem de conter todos os dias de cada ano. Se atualizar regularmente os seus dados com dados mais recentes, poderá querer executar a data de fim por um ou dois anos, para que não tenha de atualizar a tabela de datas à medida que a hora passa.
Tabela de datas com um conjunto contíguo de datas
Se reportar um ano fiscal, pode criar uma tabela de datas com um conjunto contíguo de datas para cada ano fiscal. Por exemplo, se o seu ano fiscal começar a 1 de março e tiver dados para os anos fiscais de 2010 até à data atual (por exemplo, em FY 2013), pode criar uma tabela de datas que começa em 01/03/2009 e inclui, pelo menos, todos os dias em cada ano fiscal até à última data do Ano Fiscal de 2013.
Se apresentar relatórios sobre o ano civil e o ano fiscal, não precisa de criar tabelas de datas separadas. Uma única tabela de datas pode incluir colunas para um ano civil, ano fiscal e até mesmo um calendário de treze período de quatro semanas. O importante é que a sua tabela de datas contém um conjunto contíguo de datas para todos os anos incluído.
Adicionar uma tabela de datas ao Modelo de Dados
Existem várias formas de adicionar uma tabela de datas ao seu Modelo de Dados:
- Importar a partir de uma base de dados relacional ou de outra origem de dados.
- Crie uma tabela de datas no Excel e, em seguida, copie ou ligue a uma nova tabela no Power Pivot.
- Importar do Microsoft Azure Marketplace.
Vamos analisar cada um destes mais detalhadamente.
Importar a partir de uma base de dados relacional
Se importar alguns ou todos os seus dados de um armazém de dados ou de outro tipo de base de dados relacional, é provável que já exista uma tabela de datas e relações entre os mesmos e os restantes dados que está a importar. As datas e o formato provavelmente corresponderão às datas nos seus dados de factos, e as datas provavelmente começam bem no passado e vão muito para o futuro. A tabela de datas que pretende importar pode ser muito grande e conter um intervalo de datas para além do que terá de incluir no seu Modelo de Dados. Pode utilizar as funcionalidades de filtro avançadas do Assistente de Importação de Tabelas do Power Pivot para escolher seletivamente apenas as datas e as colunas específicas de que realmente precisa. Isto pode reduzir significativamente o tamanho do livro e melhorar o desempenho.
Assistente de Importação de Tabela
Na maioria dos casos, não terá de criar colunas adicionais, como Ano Fiscal, Semana, Nome do Mês, etc. porque já existirão na tabela importada. No entanto, em alguns casos, depois de importar a tabela de datas para o modelo de dados, poderá ter de criar colunas de data adicionais, consoante uma determinada necessidade de relatórios. Felizmente, isto é fácil de fazer com o DAX. Irá saber mais sobre a criação de campos de tabela de datas mais tarde. Cada ambiente é diferente. Se não tiver a certeza se as origens de dados têm uma data ou tabela de calendário relacionada, fale com o administrador da base de dados.
Criar uma tabela de datas no Excel
Pode criar uma tabela de datas no Excel e, em seguida, copiá-la para uma nova tabela no Modelo de Dados. Isto é realmente muito fácil de fazer e dá-lhe muita flexibilidade.
Quando cria uma tabela de datas no Excel, começa com uma única coluna com um intervalo contíguo de datas. Em seguida, pode criar colunas adicionais, como Ano, Trimestre, Mês, Ano Fiscal, Período, etc. na folha de cálculo do Excel com fórmulas do Excel ou, depois de copiar a tabela para o Modelo de Dados, pode criá-las como colunas calculadas. A criação de colunas de data adicionais no Power Pivot é descrita na secção Adicionar Novas Colunas de Data à Tabela de Datas mais adiante neste artigo.
Como: Criar uma tabela de datas no Excel e copiá-la para o Modelo de Dados
No Excel, numa folha de cálculo em branco, na célula A1, escreva um nome de cabeçalho de coluna para identificar um intervalo de datas. Normalmente, será algo como Data, DateTime ou DateKey.
Na célula A2, escreva uma data de início. Por exemplo, 1/1/2010.
Clique na alça de preenchimento e arraste-a para baixo para um número de linha que inclua uma data de fim. 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, em seguida, selecione um estilo.
Na caixa de diálogo Formatar como Tabela, clique em OK.
Copie todas as linhas, incluindo o cabeçalho.
No Power Pivot, no separador Base , clique em Colar.
Em ColarNome da Tabela de Pré-visualização>, escreva um nome como Data ou Calendário. Deixe Utilizar a primeira linha como cabeçalhos de coluna selecionadose, em seguida, clique em OK.
A nova tabela de datas (denominada Calendário neste exemplo) no Power Pivot tem o seguinte aspeto:
Observação
Também pode criar uma tabela ligada com a opção Adicionar ao Modelo de Dados. No entanto, isto torna o seu livro desnecessariamente grande porque o livro tem duas versões da tabela de datas; um no Excel e outro no Power Pivot.
Observação
A data do nome é uma palavra-chave no Power Pivot. Se atribuir um nome à tabela que criar na Data do Power Pivot, terá de incluir o nome da tabela com plicas em quaisquer fórmulas DAX que a referenciem num argumento. Todas as imagens e fórmulas de exemplo neste artigo referem-se a uma tabela de datas criada no Power Pivot chamada Calendário.
Tem agora uma tabela de datas no seu Modelo de Dados. Pode adicionar novas colunas de data, como Ano, Mês, etc. com o DAX.
Adicionar novas colunas de data à tabela de datas
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 num intervalo de datas. Também é necessário criar uma relação entre a tabela de factos e a tabela de datas. No entanto, essa coluna de data única com uma linha para cada dia não é útil ao analisar por datas num relatório de Tabela Dinâmica ou do Power View. Pretende que a tabela de datas inclua colunas que o ajudem a agregar os seus dados para um intervalo ou grupo de datas. Por exemplo, poderá querer somar os valores das vendas por mês ou trimestre ou pode criar uma medida que calcula o crescimento ao longo do ano. Em cada um destes casos, a tabela de datas precisa de colunas de ano, mês ou trimestre que lhe permitam agregar os seus dados para esse período.
Se tiver importado a tabela de datas de uma origem de dados relacional, esta poderá já incluir os diferentes tipos de colunas de data que pretende. Em alguns casos, poderá querer modificar algumas dessas colunas ou criar colunas de data adicionais. Isto é especialmente verdade se criar a sua própria tabela de datas no Excel e copiá-la para o Modelo de Dados. Felizmente, criar novas colunas de data no Power Pivot é bastante fácil com as Funções de Data e Hora no DAX.
Dica
Se ainda não trabalhou com o DAX, um ótimo local para começar a aprender é com o Início Rápido: Aprender noções Básicas do DAX em 30 Minutos no Office.com.
Funções de Data e Hora do DAX
Se já trabalhou com funções de data e hora em fórmulas do Excel, é provável que esteja familiarizado com as Funções de Data e Hora. Embora estas funções sejam semelhantes às suas equivalentes no Excel, existem algumas diferenças importantes:
- As funções Data e Hora do DAX utilizam um tipo de dados datetime.
- Podem utilizar valores de uma coluna como argumento.
- Podem ser utilizadas para devolver e/ou manipular valores de data.
Estas funções são frequentemente utilizadas ao criar colunas de data personalizadas numa tabela de datas, pelo que são importantes de compreender. Vamos utilizar várias destas funções para criar colunas para Ano, Trimestre, MêsFiscal, etc.
Observação
As funções Data e Hora no DAX não são as mesmas que as funções de Análise de Tempo. Saiba mais sobre a Análise de Tempo 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
- TIME
- VALOR.TEMPO
- HOJE
- WEEKDAY
- WEEKNUM
- YEAR
- YEARFRAC
Também pode utilizar muitas outras funções DAX nas suas fórmulas. Por exemplo, muitas das fórmulas descritas aqui utilizam Funções Matemáticas e Trigonométricas , como MOD e TRUNC, Funções Lógicas como SE e Funções de Texto como FORMAT Para obter mais informações sobre outras funções DAX, consulte a secção Recursos Adicionais mais adiante neste artigo.
Exemplos de fórmulas para um ano de calendário
Os exemplos seguintes descrevem fórmulas utilizadas para criar colunas adicionais numa tabela de datas denominada Calendário. Uma coluna, denominada Data, já existe e contém um intervalo contíguo de datas de 1/1/2010 a 31/12/2016.
Ano
=ANO([data])
Nesta fórmula, a função ANO devolve o ano do valor na coluna Data. Uma vez que o valor na coluna Data é do tipo de dados datetime, a função ANO sabe como devolver o ano da mesma.
Mês
=MÊS([data])
Nesta fórmula, tal como acontece com a função ANO, podemos simplesmente utilizar a função MÊS para devolver um valor mensal da coluna Data.
Trimestre
=INT(([Mês]+2)/3)
Nesta fórmula, utilizamos a função INT para devolver 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, em seguida, divida-o por 3 para obter o nosso trimestre, 1 a 4.
Mês Nome
=FORMAT([date],"mmmm")
Nesta fórmula, para obter o nome do mês, utilizamos a função FORMAT 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 nosso mês mostre todos os carateres, pelo que utilizamos "mmmm". O nosso resultado tem o seguinte aspeto:
Se quisermos devolver o nome do mês abreviado para três letras, utilizaríamos "mmm" no argumento de formato.
Dia da semana
=FORMAT([date],"ddd")
Nesta fórmula, utilizamos a função FORMAT para obter o nome do dia. Como só queremos um nome de dia abreviado, especificamos "ddd" no argumento de formato.
Exemplo de Tabela Dinâmica
Depois de ter campos para datas como Ano, Trimestre, Mês etc., você pode usá-los em uma Tabela Dinâmica ou relatório. Por exemplo, a imagem a seguir mostra o campo SalesAmount da tabela De fatos de vendas em VALUES e Ano e Trimestre da tabela de dimensões calendário em ROWS. SalesAmount é agregado para o contexto ano e trimestre.
Exemplos de fórmula para um ano fiscal
Fiscal Year
=IF([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 são muitas vezes diferentes das de um ano civil. Para obter o ano fiscal, primeiro usamos uma função IF para testar se o valor de Mês é menor ou igual a 6. No segundo argumento, se o valor de Mês for menor ou igual a 6, retornará o valor da coluna Ano. Caso contrário, retorne o valor de Ano e adicione 1.
Outra maneira de especificar um valor do mês de fim de ano fiscal é criar uma medida que simplesmente especifica 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, =IF([Month]<=[FYE],[Year],[Year]+1). Isso fornece mais flexibilidade ao referenciar o mês de final do ano fiscal em várias fórmulas diferentes.
Mês Fiscal
=IF([Mês]<= 6, 6+[Mês], [Mês]- 6)
Nesta fórmula, especificamos se o valor de [Mês] é menor ou igual a 6, em seguida, tomamos 6 e adicionamos o valor de Mês, caso contrário, subtraimos 6 do valor de [Mês].
Fiscal Quarter
=INT(([FiscalMonth]+2)/3)
A fórmula que usamos para FiscalQuarter é a mesma do trimestre em nosso ano civil. A única diferença é que especificamos [FiscalMonth] em vez de [Mês].
Feriados ou datas especiais
Você pode querer incluir uma coluna de data que indica que determinadas datas são feriados ou alguma outra data especial. Por exemplo, você pode querer somar totais de vendas para o Dia de Ano Novo adicionando um campo Holiday a uma Tabela Dinâmica, como uma segmentação ou filtro. Em outros casos, você pode querer 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 que tenha 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 Calendário. Todas as fórmulas que fazem referência a ela podem usar a função LOOKUPVALUE para retornar valores.
Veja abaixo um exemplo de uma tabela criada no Excel que inclui feriados a serem adicionados à tabela de datas:
| Data | Feriado |
|---|---|
| 1/1/2010 | Anos Novos |
| 11/25/2010 | Thanksgiving |
| 12/25/2010 | Natal |
| 01.01.11 | Anos Novos |
| 11/24/2011 | Thanksgiving |
| 12/25/2011 | Natal |
| 01.01.12 | Anos Novos |
| 22.11.12 | Thanksgiving |
| 12/25/2012 | Natal |
| 1/1/2013 | Anos Novos |
| 11/28/2013 | Thanksgiving |
| 12/25/2013 | Natal |
| 11/27/2014 | Thanksgiving |
| 12/25/2014 | Natal |
| 01/01/2014 | Anos Novos |
| 11/27/2014 | Thanksgiving |
| 12/25/2014 | Natal |
| 1/1/2015 | Anos Novos |
| 11/26/2014 | Thanksgiving |
| 12/25/2015 | Natal |
| 01.01.16 | Anos Novos |
| 11/24/2016 | Thanksgiving |
| 12/25/2016 | Natal |
Na tabela de datas, criamos uma coluna chamada Holiday e usamos uma fórmula como esta:
=LOOKUPVALUE(Feriados[Feriado],Feriados[data],Calendário[data])
Vamos examinar essa fórmula com mais cuidado.
Usamos a função LOOKUPVALUE para obter valores da coluna Holiday na tabela Feriados. No primeiro argumento, especificamos a coluna em que nosso valor de resultado será. Especificamos a coluna Holiday na tabela Feriados porque esse é o valor que queremos que seja retornado.
=LOOKUPVALUE(Feriados[Feriado],Feriados[data],Calendário[data])
Em seguida, especificamos o segundo argumento, a coluna de pesquisa que tem as datas que desejamos pesquisar. Especificamos a coluna Data na tabela Feriados , assim:
=LOOKUPVALUE(Feriados[Feriado],Feriados[data],Calendário[data])
Por fim, especificamos a coluna em nossa tabela Calendário que tem as datas que desejamos pesquisar na tabela Feriado . É claro que esta é a coluna Data na tabela Calendário .
=LOOKUPVALUE(Feriados[Feriados],Feriados[data],Calendar[data])
A coluna Holiday 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ço de alimentação, geralmente relatam períodos diferentes, como treze períodos de quatro semanas. Com um calendário de treze 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 cairão no mesmo período a cada ano. Você pode optar por iniciar um período em qualquer dia da semana. Assim como acontece com 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 7/1.
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 no primeiro ano fiscal da tabela Calendar realmente começa 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([date]-40356)/7)
Vamos examinar essa fórmula com mais cuidado.
Primeiro, criamos uma fórmula que retorna valores da coluna Data como um inteiro, assim:
=INT([date])
Em seguida, queremos procurar o primeiro domingo no primeiro ano fiscal. Vemos que é 7/4/2010.
Agora, subtraia 40356 (que é o 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 de Calendar, assim:
=INT([date]-40356)
Em seguida, divida o resultado por 7 (dias em uma semana), assim:
=INT(([date]-40356)/7)
O resultado se parece com este:
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 começando com o primeiro domingo no primeiro ano fiscal.
=INT(([Semana]+3)/4)
Vamos examinar essa fórmula com mais cuidado.
Primeiro, criamos uma fórmula que retorna um valor da coluna Semana como um inteiro, assim:
= INT([Semana])
Em seguida, adicione 3 a esse valor, assim:
=INT([Semana]+3)
Em seguida, divida o resultado por 4, assim:
=INT(([Semana]+3)/4)
O resultado se parece com este:
Ano Fiscal do Período
Esse valor retorna o ano fiscal por um período.
=INT(([Period]+12)/13)+2008
Vamos examinar essa fórmula com mais cuidado.
Primeiro, criamos uma fórmula que retorna um valor de Period e adiciona 12:
=([Period]+12)
Dividimos o resultado por 13, porque há treze períodos de 28 dias no ano fiscal:
=(([Period]+12)/13)
Adicionamos 2010, porque esse é o primeiro ano na tabela:
=(([Period]+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([Period]+12)/13)+2010
O resultado se parece com este:
Período no Ano Fiscal
Esse valor retorna o número do período, 1 a 13, começando com o primeiro Período completo (começando no domingo) em cada ano fiscal.
=IF(MOD([Period],13), MOD([Period],13),13)
Essa fórmula é um pouco mais complexa, por isso vamos descrevê-la primeiro em uma linguagem que entendemos melhor. Essa fórmula afirma, 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ática e Trigonométrica) assim:
= MOD([Period],13)
Isso, na maioria das vezes, nos dá o resultado que queremos, exceto quando o valor para Period é 0 porque essas datas não se enquadram no primeiro ano fiscal, como nos primeiros cinco dias de nosso exemplo Calendar tabela de datas. Podemos cuidar disso com uma função IF. Caso nosso resultado seja 0, retornaremos 13, assim:
= IF(MOD([Period],13),MOD([Period],13),13)
O resultado se parece com este:
Exemplo de Tabela Dinâmica
A imagem abaixo mostra uma Tabela Dinâmica com o campo SalesAmount da tabela de fatos Vendas em VALUES e campos PeriodFiscalYear e PeriodInFiscalYear da tabela de dimensão de data Calendar em ROWS. 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 em seu Modelo de Dados, para começar a navegar seus dados em Tabelas Dinâmicas e relatórios e agregar dados com base nas colunas na tabela de dimensão de data, você precisa criar uma relação entre a tabela de fatos com os dados de transação e a tabela de data.
Como você precisa criar uma relação com base em datas, você vai querer ter certeza de criar essa relação entre colunas cujos valores são do tipo de dados datetime (Date).
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 de fatos Sales com um valor de 15/08/2012 12:00 na coluna DateKey deve ter um valor correspondente na coluna Data relacionada na tabela data (nomeada Calendar). Essa é uma das razões mais importantes pelas quais você deseja que sua coluna de data na tabela de datas contenha um intervalo contíguo de datas que inclua qualquer data possível em sua 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 no mesmo nível de precisão. Dependendo da formatação da coluna, os valores podem ser iguais, mas armazenados de forma diferente. Leia mais sobre como trabalhar com o tempo.
Observação
Evite usar chaves substitutas inteiros em relacionamentos. Quando você importa dados de uma fonte de dados relacional, muitas vezes as colunas de data e hora são representadas por uma chave substituta, que é uma coluna inteiro usada para representar uma data exclusiva. No Power Pivot, você deve evitar criar relações usando chaves de data/hora inteiros e, em vez disso, usar colunas que contenham valores exclusivos com um tipo de dados de data. Embora o uso de chaves substitutas seja considerado uma prática recomendada em data warehouses tradicionais, as chaves inteiros 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 porque a coluna na tabela de fatos não é do tipo de dados Date. Isso pode acontecer quando o Power Pivot não pode converter automaticamente uma não data (geralmente um tipo de dados de texto) em um tipo de dados de data. Você ainda pode usar a coluna na tabela de fatos, mas precisará converter os dados com uma fórmula DAX em uma nova coluna calculada. Consulte Converter datas de tipo de dados de texto em um tipo de dados de data posterior no apêndice.
Várias relações
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 Date na tabela de datas Calendar, mas apenas um deles pode 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 relações inativas.
A Tabela Dinâmica a seguir calcula o total de vendas por Ano Fiscal e Trimestre Fiscal. Uma medida denominada Vendas Totais, com a fórmula Total Sales:=SUM([SalesAmount]), é colocada em VALORES, e os campos FiscalYear e FiscalQuarter da tabela de datas calendário são colocados em LINHAS.
de 
de tabela dinâmica do trimestre fiscal
Essa Tabela Dinâmica direta funciona corretamente porque queremos somar nossas vendas totais pelo transadate no DateKey. Nossa medida Vendas Totais usa as datas no DateKey e é resumida por ano fiscal e trimestre fiscal porque há uma relação entre DateKey na tabela Vendas e a coluna Data na tabela de datas calendário.
Relações inativas
Mas, e se quiséssemos resumir nossas vendas totais não por data de transação, mas por data de envio? Precisamos de uma relação entre a coluna ShipDate na tabela Vendas e a coluna Data na tabela Calendário. 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 Calendário.
Nesse caso, o ShipDate tem uma relação inativa, portanto, qualquer fórmula de medida criada para agregar dados com base em datas de navio deve especificar a relação inativa usando a função USERELATIONSHIP .
Por exemplo, como há uma relação inativa entre a coluna ShipDate na tabela Vendas e a coluna Data na tabela Calendário, 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:
Vendas totais por data do navio:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Essa fórmula simplesmente afirma: calcular uma soma para SalesAmount, mas filtrar usando a relação entre a coluna ShipDate na tabela Vendas e a coluna Data na tabela Calendário.
Agora, se criarmos uma Tabela Dinâmica e colocarmos a medida Total de Vendas por Data de Navio em VALORES e Ano Fiscal e Trimestre Fiscal em LINHAS, veremos o mesmo Grande Total, mas todos os outros valores de soma para o ano fiscal e o trimestre fiscal são diferentes porque são baseados na data do navio e não na data da transação.
O uso de relações inativas permite que você use apenas uma tabela de datas, mas exige que todas as medidas (como Total de Vendas por Data de Navio), referenciem a relação inativa em sua fórmula. Há 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 na tabela de fatos é criar várias tabelas de datas e criar relações ativas separadas entre elas. Vamos examinar nosso exemplo de tabela de vendas novamente. Temos três colunas com datas em que talvez queiramos agregar dados:
- Um DateKey com a data de venda para cada transação.
- Um ShipDate – com a data e a hora em que os itens vendidos foram enviados para o cliente.
- Um ReturnDate – com a data e a hora em que um ou mais itens retornados foram recebidos.
Lembre-se de que o campo DateKey com a data da transação é mais importante. Faremos a maioria de nossas agregações com base nessas datas, portanto, certamente queremos uma relação entre ela e a coluna Data na tabela Calendário. Se não quisermos criar relações inativas entre ShipDate e ReturnDate e o campo Data na tabela Calendário, exigindo assim fórmulas de medidas especiais, podemos criar tabelas de datas adicionais para data de envio e data de retorno. Em seguida, podemos criar relações ativas entre eles.
Neste exemplo, criamos outra tabela de datas chamada ShipCalendar. Isso, é claro, também significa criar colunas de datas adicionais e, como essas colunas de data estão em uma tabela de datas diferente, queremos nomeá-las de maneira que as diferencie das mesmas colunas na tabela Calendário. Por exemplo, criamos colunas chamadas ShipYear, ShipMonth, ShipQuarter e assim por diante.
Se criarmos nossa Tabela Dinâmica e colocarmos nossa medida de Vendas Totais em VALUES e ShipFiscalYear e ShipFiscalQuarter em LINHAS, veremos os mesmos resultados que vimos quando criamos uma relação inativa e um campo calculado total de Vendas por Data de Navio especial.
de 
do calendário do navio
Cada uma dessas abordagens requer uma consideração cuidadosa. Ao usar várias relações com uma única tabela de datas, talvez seja necessário criar medidas especiais que transitem em relações inativas usando a função USERELATIONSHIP. Por outro lado, criar várias tabelas de datas pode ser confuso 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ê.
Propriedade Date Table
A propriedade Tabela de Data define 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 para onde ir para obter as datas necessárias.
Aviso
Se essa propriedade não estiver definida, as medidas usando o DAX Time-Intelligence funções poderão não retornar resultados corretos.
Ao definir a propriedade Date Table, você especifica uma tabela de datas e uma coluna de data do tipo de dados Date (datetime) nela.
Como definir a propriedade Tabela de Data
- Na janela PowerPivot, selecione a tabela Calendário .
- Na guia Design , clique em Marcar como tabela de datas.
- Na caixa de diálogo Marcar como Tabela de Data, selecione uma coluna com valores exclusivos e o tipo de dados Date.
Trabalhando com o tempo
Todos os valores de data com um tipo de dados Date no Excel ou NO SQL Server são, na verdade, um número. Incluídos nesse número estão dígitos que se referem a uma hora. Em muitos casos, essa hora 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 diurno de precisão. Se os valores de campo DateTimeKey tiverem um tempo incluído, por exemplo, 19/10/2010 8:44:00 AM, isso significa que os valores estão no nível de precisão de minuto. Os valores também podem ser para a precisão do nível da hora ou até mesmo para o nível de precisão de segundos. O nível de precisão no valor de tempo terá um impacto significativo na forma como você cria sua tabela de datas e as relações entre ela e sua tabela de fatos.
Você precisa determinar se você agregará seus dados a um nível diário de precisão ou a um nível de tempo de precisão. Em outras palavras, talvez você queira usar colunas em sua tabela de datas, como campos de data da manhã, tarde ou hora como data de hora em áreas de Linha, Coluna ou Filtro de Tabela Dinâmica.
Observação
Os dias são a menor unidade de tempo com a qual as funções do DAX Time Intelligence podem funcionar. Se você não precisar trabalhar com valores de tempo, deverá 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 hora, sua tabela de datas 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é mesmo 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 datas, você deve ter valores correspondentes. Como você pode imaginar, se você incluir muitos anos, isso pode fazer uma tabela de datas muito grande.
Na maioria dos casos, porém, você deseja agregar seus dados apenas ao dia. Em outras palavras, você usará colunas como Ano, Mês, Semana ou Dia da Semana como campos em áreas de Linha, Coluna ou Filtro de Tabela Dinâmica. Nesse caso, a coluna de data na tabela de datas precisa conter apenas uma linha para cada dia em um ano, como descrevemos anteriormente.
Se sua coluna de data incluir um nível de tempo de precisão, 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 trunque os valores na coluna de data para um valor diário. Em outras palavras, converta um valor como 19/10/2010 8:44:00 para19/10/2010 12:00:00. Em seguida, você pode criar a relação entre essa nova coluna e a coluna de data na tabela de datas porque os valores correspondem.
Vamos ver um exemplo. Esta imagem mostra uma coluna DateTimeKey na tabela De fatos de Vendas. Todas as agregações para os dados nesta tabela só precisam ser para o nível do dia, usando colunas na tabela de datas calendário 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 para o nível de tempo, não precisamos da coluna Data na tabela de datas calendário para incluir uma linha para cada hora e cada minuto de cada dia em cada ano. Portanto, a coluna Data em nossa tabela de datas tem a seguinte aparência:
Para criar uma relação entre a coluna DateTimeKey na tabela Vendas e a coluna Data na tabela Calendário, podemos criar uma nova coluna calculada na tabela de fatos Vendas 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 Data na tabela Calendário. Nossa fórmula se parece com esta:
=TRUNC([DateTimeKey],0)
Isso nos dá uma nova coluna (chamamos DateKey) com a data da coluna DateTimeKey e uma hora das 12:00:00 para cada linha:
Agora podemos criar uma relação entre essa nova coluna (DateKey) e a coluna Data na tabela Calendário.
Da mesma forma, podemos criar uma coluna calculada na tabela Vendas que reduz a precisão de tempo na coluna DateTimeKey para o nível de hora de precisão. Nesse caso, a função TRUNC não funcionará, mas ainda podemos usar outras funções DAX Date e Time para extrair e recontentar um novo valor para um nível de hora de precisão. Podemos usar uma fórmula como esta:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Nossa nova coluna se parece com esta:
Desde que nossa coluna Date na tabela de datas tenha valores para o nível de hora de precisão, podemos criar uma relação entre eles.
Tornando as datas mais utilizáveis
Muitas das colunas de data que você cria na tabela de datas 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 Vendas a que 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 de análise e 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 Calendário é muito útil, de fato crítica, mas você não pode usá-la como uma dimensão em uma Tabela Dinâmica.
Para manter as tabelas e as colunas nas mesmas o mais úteis possível e para facilitar a navegação das listas de Campos da Tabela Dinâmica ou do relatório do Power View, é importante ocultar colunas desnecessárias das ferramentas de cliente. Também pode querer ocultar determinadas tabelas. A tabela Feriados apresentada anteriormente contém datas de feriados que são importantes para determinadas colunas na tabela Calendário, mas não pode utilizar as colunas Data e Feriado na tabela Feriados como campos numa tabela dinâmica. Aqui novamente, para facilitar a navegação nas Listas de Campos, pode ocultar toda a tabela Feriados.
Outro aspeto importante do trabalho com datas são as convenções de nomenclatura. Pode atribuir nomes a tabelas e colunas no Power Pivot como quiser. No entanto, tenha em atenção, especialmente se quiser partilhar o seu livro com outros utilizadores, uma boa convenção de nomenclatura facilita a identificação de tabelas e datas, não só em Listas de Campos, mas também no Power Pivot e em fórmulas DAX.
Depois de ter uma tabela de datas no seu Modelo de Dados, pode começar a criar medidas que o ajudarão a tirar o máximo partido dos seus dados. Alguns podem ser tão simples como somar totais de vendas para o ano atual e outros podem ser mais complexos, onde tem de filtrar um determinado intervalo de datas exclusivas. Saiba mais em Medidas no Power Pivot e funções de Análise de Tempo.
Apêndice
Converter datas de tipo de dados de texto num tipo de dados de data
Em alguns casos, uma tabela de factos 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 não é, de facto, uma data ou pelo menos não é o tipo de data que o Power Pivot consegue compreender. É apenas texto que se lê como uma data. Para criar uma relação entre uma coluna de data na tabela de factos e uma coluna de data numa tabela de datas, ambas as colunas têm de ser do tipo de dados Data .
Normalmente, quando tenta alterar o tipo de dados de uma coluna de datas que são tipo de dados de texto para um tipo de dados de data, o Power Pivot pode interpretar as datas e convertê-la automaticamente num tipo de dados de data verdadeiro. Se o Power Pivot não conseguir efetuar uma conversão de tipo de dados, obterá um erro de correspondência de tipo.
No entanto, ainda pode converter as datas num tipo de dados de data verdadeiro. Pode criar uma nova coluna calculada e utilizar uma fórmula DAX para analisar o ano, mês, dia, hora, etc. a partir das cadeias de texto e concatenar novamente de uma forma que o Power Pivot possa ler como uma data verdadeira.
Neste exemplo, importámos uma tabela de factos denominada Vendas para o Power Pivot. Contém uma coluna denominada DateTime. Os valores são apresentados da seguinte forma:
Se observarmos o Tipo de Dados no separador Base do grupo Formatação do Power Pivot, vemos que se trata de Tipo de dados de texto.
Não conseguimos criar uma relação entre a coluna DateTime e a coluna Date na nossa tabela de datas porque os tipos de dados não correspondem. Se tentarmos alterar o tipo de dados para Data, obtemos um erro de correspondência de tipo:
Neste caso, o Power Pivot não conseguiu converter o tipo de dados de texto para data. Ainda podemos utilizar esta coluna, mas para a colocar num tipo de dados de data verdadeiro, precisamos de criar uma nova coluna que analise o texto e o recrie num valor que o Power Pivot possa criar um tipo de dados Data.
Lembre-se, na secção Trabalhar com o tempo anteriormente neste artigo; a menos que seja necessário que a sua análise seja para um nível de precisão de hora do dia, deve converter datas na sua tabela de factos para um nível de precisão diário. Com isso em mente, queremos que os valores na nossa nova coluna estejam no nível de precisão diária (excluindo o tempo). Ambos podemos converter os valores na coluna DateTime num tipo de dados de data e remover o nível de tempo de precisão com a seguinte fórmula:
=DATA(ESQUERDA([DateTime];4), MID([DateTime];6;2), MID([DateTime],9,2))
Isto dá-nos uma nova coluna (neste caso, denominada Data). O Power Pivot até deteta os valores como datas e define o tipo de dados automaticamente como Data.
Se quisermos preservar o nível de tempo de precisão, basta alargar a fórmula para incluir as horas, minutos e 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 a mesma e uma coluna de data numa data.
Recursos adicionais
Início rápido: aprenda os fundamentos de DAX em 30 minutos