As tabelas de dados no Power Pivot são essenciais para procurar e calcular dados ao longo do tempo. Este artigo fornece uma compreensão completa das tabelas de data e como pode criá-las no Power Pivot. Em particular, este artigo descreve:
- Por que uma tabela de data é importante para navegar e calcular dados por datas e horas.
- Como utilizar 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, numa tabela de datas.
- Como criar relações entre tabelas de data e tabelas de factos.
- Como trabalhar com o tempo.
Este artigo destina-se a utilizadores que não conhecem o Power Pivot. No entanto, é importante que já compreenda bem a importação de dados, a criação de relações e a criação de colunas e medidas calculadas.
Este artigo não descreve como utilizar funções DAX Time-Intelligence em fórmulas de medida. Para obter mais informações sobre como criar medidas com funções de Análise de Tempo do DAX, consulte Análise de Tempo no Power Pivot no Excel.
Nota
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.
Conteúdos
Compreender as tabelas de datas
Quase toda a análise de dados envolve a navegação e a comparação de dados ao longo de datas e horas. Por exemplo, poderá querer somar os montantes de vendas do último trimestre fiscal e, em seguida, comparar esses totais com outros trimestres ou calcular o saldo de fecho de fim de mês de uma conta. Em cada um destes casos, está a utilizar datas como uma forma de agrupar e agregar transações de vendas ou saldos para um determinado período no tempo.
Relatório do Power View
Uma tabela de data pode conter várias representações diferentes de datas e horas. Por exemplo, uma tabela de datas tem normalmente colunas como Ano Fiscal, Mês, Trimestre ou Período, que pode selecionar como campos de uma Lista de Campos ao segmentar e filtrar os seus dados em relatórios de Tabelas Dinâmicas ou Power View.
Lista de Campos do Power View
Para que as colunas de data, como Ano, Mês e Trimestre, incluam todas as datas nos respetivos intervalos, a tabela de datas tem de ter, pelo menos, uma coluna com um conjunto contínuo de datas. Ou seja, essa coluna tem de ter uma linha para cada dia e cada ano incluído na tabela de datas.
Por exemplo, se os dados que pretende procurar datarem de 1 de fevereiro de 2010 a 30 de novembro de 2012 e comunicar um ano de calendário, então irá querer 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 na sua tabela de datas tem de conter todos os dias de cada ano. Se atualiza regularmente os seus dados com dados mais recentes, pode querer terminar a data de fim daqui a um ou dois anos, para não ter de atualizar a sua tabela de datas com o passar do tempo.
Tabela de datas com um conjunto contínuo de datas
Se comunicar um ano fiscal, pode criar uma tabela de data 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 dos exercícios de 2010 atualizados até à data atual (por exemplo, no ano fiscal de 2013), pode criar uma tabela de datas que comece a 1/3/2009 e inclua pelo menos todos os dias em cada ano fiscal até à última data do ano fiscal de 2013.
Se pretende comunicar tanto no ano civil como no ano fiscal, não precisa de criar tabelas de datas separadas. Uma única tabela de data pode incluir colunas para um ano civil, ano fiscal e até treze calendários de 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ídos.
Adicionar uma tabela de data ao Modelo de Dados
Existem várias formas de adicionar uma tabela de data ao seu Modelo de Dados:
- Importar a partir de uma base de dados relacional ou de outra origem de dados.
- Crie uma tabela de data no Excel e, em seguida, copie ou ligue a uma nova tabela no Power Pivot.
- Importe a partir do Microsoft Azure Marketplace.
Vamos olhar para cada um deles mais de perto.
Importar a partir de uma base de dados relacional
Se importar alguns ou todos os seus dados de um armazém de dados ou outro tipo de base de dados relacional, o mais provável é que já exista uma tabela de datas e relações entre esta e o resto dos dados que está a importar. As datas e o formato provavelmente coincidirão com as 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 data 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 avançadas de filtragem do Assistente de Importação de Tabelas do Power Pivot para escolher seletivamente apenas as datas e as colunas específicas de que realmente necessita. Isto pode reduzir significativamente o tamanho do livro e melhorar o desempenho.
Assistente de Importação de Tabelas
Na maioria dos casos, não será necessário criar colunas adicionais, como Ano Fiscal, Semana, Nome do Mês, etc., pois já existirão na tabela importada. No entanto, em alguns casos, depois de importar a tabela de data para o seu Modelo de Dados, poderá ter de criar colunas de datas adicionais, consoante uma necessidade específica do relatório. Felizmente, é fácil fazê-lo com DAX. Saberá mais sobre como criar campos de tabela de data posteriormente. Cada ambiente é diferente. Se não tiver a certeza se as suas 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 data no Excel
Pode criar uma tabela de data 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ínuo de datas. Em seguida, pode criar colunas adicionais tais como Ano, Trimestre, Mês, Ano Fiscal, Período, etc. na folha de cálculo do Excel utilizando 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 data 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, DataHora ou ChaveDeData.
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.
Selecionar 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.
Copie todas as linhas, incluindo o cabeçalho.
No Power Pivot, no separador Base , clique em Colar.
Em Colar Pré-visualização>Nome da Tabela, introduza um nome, como Data ou Calendar. Deixe a opção Usar primeira linha como cabeçalhos de colunamarcada e clique em OK.
A nova tabela de data (denominada Calendar neste exemplo) no Power Pivot tem o seguinte aspeto:
Nota
Também pode criar uma tabela ligada ao utilizar a opção Adicionar ao Modelo de Dados. No entanto, isto torna o seu livro desnecessariamente grande porque tem duas versões da tabela de datas; um no Excel e um no Power Pivot.
Nota
A data do nome é uma palavra-chave no Power Pivot. Se atribuir um nome à tabela que criou na Data do Power Pivot, então terá de colocar o nome da tabela entre aspas simples em todas as fórmulas DAX que façam referência à mesma num argumento. Todas as imagens e fórmulas de exemplo neste artigo referem-se a uma tabela de data criada no Power Pivot com o nome Calendar.
Agora tem uma tabela de data no seu Modelo de Dados. Pode adicionar novas colunas de data, como Ano, Mês, etc., através de DAX.
Adicionar novas colunas de data à tabela de datas
Uma tabela de data com uma única coluna de data que tenha uma linha para cada dia de cada ano é importante para definir todas as datas num intervalo de datas. Também é necessário para criar uma relação entre a tabela de factos e a tabela de datas. Mas essa coluna de data única com uma linha para cada dia não é útil quando analisar datas num relatório de Tabela Dinâmica ou Power View. Pretende que a tabela de datas inclua colunas que o ajudem a agregar os dados para um intervalo ou grupo de datas. Por exemplo, poderá querer somar os montantes de vendas por mês ou trimestre ou criar uma medida que calcule o crescimento anual em relação ao ano anterior. Em cada um destes casos, a sua tabela de datas precisa de colunas de ano, mês ou trimestre que lhe permitam agregar os dados para esse período.
Se importou a sua tabela de data a partir de uma origem de dados relacional, esta já poderá 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 aplica-se especialmente se criar a sua própria tabela de data no Excel e a copiar para o Modelo de Dados. Felizmente, com as Funções de Data e Hora do DAX, é bastante fácil criar novas colunas de data no Power Pivot.
Sugestão
Se você ainda não trabalhou com o DAX, um ótimo lugar para começar a aprender é com o QuickStart: Aprenda 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, provavelmente estará familiarizado com as funções de data e hora. Embora estas funções sejam semelhantes às suas congéneres do Excel, existem algumas diferenças importantes:
- As funções do DAX de Data e Hora utilizam um tipo de dados datetime.
- Podem tomar valores de uma coluna como um 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 é importante compreendê-las. Iremos utilizar várias destas funções para criar colunas para Ano, Trimestre, MêsFiscal, entre outros.
Nota
As funções de Data e Hora no DAX não são iguais às 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
- DATAM
- FIMMÊS
- HORA
- MINUTO
- MÊS
- AGORA
- SEGUNDO
- TIME
- VALOR.TEMPO
- HOJE
- DIA.SEMANA
- NÚMSEMANA
- ANO
- FRAÇÃOANO
Existem muitas outras funções do DAX que também pode utilizar nas suas fórmulas. Por exemplo, muitas das fórmulas aqui descritas utilizam Funções Matemáticas e Trigonométricas como RESTO e TRUNCO, Funções Lógicas como SE e Funções de Texto como FORMATAR Para obter mais informações sobre outras funções do DAX, consulte a secção Recursos Adicionais mais à frente 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 data denominada Calendar. Uma coluna com o nome Data já existe e contém um intervalo contíguo de datas entre 1/1/2010 e 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 a partir daí.
Mês
=MÊS([data])
Nesta fórmula, tal como na função ANO, podemos simplesmente utilizar a função MÊS para devolver um valor de mês 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 por 3 para obter o nosso trimestre, de 1 a 4.
Nome do Mês
=FORMATAR([data],"mmmm")
Nesta fórmula, para obter o nome do mês, utilizamos a função FORMATO 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 apresente todos os carateres, por isso utilizamos "mmmm". O nosso resultado tem este aspeto:
Se quisermos que o nome do mês seja abreviado para três letras, utilizaríamos "mmm" no argumento de formato.
Dia da Semana
=FORMATAR([data],"ddd")
Nesta fórmula, utilizamos a função FORMATAR para obter o nome do dia. Como queremos apenas um nome de dia abreviado, especificamos "ddd" no argumento de formato.
Tabela Dinâmica de Exemplo
Assim que tiver campos para datas como Ano, Trimestre, Mês, etc., pode utilizá-los numa Tabela Dinâmica ou relatório. Por exemplo, a imagem seguinte mostra o campo SalesAmount da tabela de factos Sales em VALUES e Year and Quarter from the Calendar dimension table in ROWS. SalesAmount é agregado para o contexto de ano e trimestre.
Exemplos de fórmulas para um ano fiscal
Ano fiscal
=SE([Mês]<= 6;[Ano];[Ano]+1)
Neste exemplo, o ano fiscal começa a 1 de julho.
Não existe nenhuma função que possa extrair um ano fiscal a partir de um valor de data porque as datas de início e fim de um ano fiscal são muitas vezes diferentes das de um ano de calendário. Para obter o ano fiscal, primeiro utilizamos uma função SE para testar se o valor para o Mês é menor ou igual a 6. No segundo argumento, se o valor do Mês for menor ou igual a 6, então, devolver o valor da coluna Ano. Caso contrário, então devolva o valor de Ano e adicione 1.
Outra forma de especificar o valor do mês de fim do ano fiscal é criar uma medida que especifique simplesmente o mês. Por exemplo, SIM:=6. Em seguida, pode referenciar o nome da medida em vez do número do mês. Por exemplo, =SE([Mês]<=[PIE],[Ano],[Ano]+1). Isto proporciona mais flexibilidade ao referenciar o mês de fim do ano fiscal em várias fórmulas diferentes.
Mês Fiscal
=SE([Mês]<= 6; 6+[Mês]- 6)
Nesta fórmula, especificamos se o valor de [Mês] é menor ou igual a 6, então tomamos 6 e adicionamos o valor de Mês, caso contrário, subtraímos 6 do valor de [Mês].
Trimestre Fiscal
=INT(([MêsFiscal]+2)/3)
A fórmula que usamos para FiscalQuarter é praticamente a mesma que era para Trimestre no nosso ano civil. A única diferença é que especificamos [MêsFiscal] em vez de [Mês].
Feriados ou datas especiais
É aconselhável incluir uma coluna de data que indique determinadas datas são feriados ou outra data especial. Por exemplo, pode querer somar os totais de vendas do dia de Ano Novo ao adicionar um campo de Feriado a uma Tabela Dinâmica, como uma segmentação de dados ou como um filtro. Noutros casos, poderá querer excluir essas datas de outras colunas de datas ou numa medida.
Incluir feriados ou dias especiais é bastante simples. Pode criar uma tabela no Excel com as datas que pretende incluir. Pode, em seguida, copiar ou utilizar a opção Adicionar ao Modelo de Dados como uma tabela ligada. Na maioria dos casos, não é necessário criar uma relação entre a tabela e a tabela Calendar. Quaisquer fórmulas que façam referência à mesma podem utilizar a função VALORPROC para devolver valores.
Segue-se 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 |
| 1/1/2011 | Ano Novo |
| 11/24/2011 | Ação de Graças |
| 12/25/2011 | Natal |
| 01/01/2012 | Ano Novo |
| 22/11/2012 | 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 |
| 1/1/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 |
| 1/1/2016 | Ano Novo |
| 11/24/2016 | Ação de Graças |
| 12/25/2016 | Natal |
Na tabela de datas, criamos uma coluna com o nome Feriado e utilizamos uma fórmula como esta:
=VALORPROC(Feriados[Feriado],Feriados[data],Calendar[data])
Vamos analisar esta fórmula com mais cuidado.
Utilizamos a função VALORPROC para obter valores a partir da coluna Feriados na tabela Feriados. No primeiro argumento, especificamos a coluna onde estará o valor do resultado. Especificamos a coluna Feriados na tabela Feriados porque é esse o valor que pretendemos que seja devolvido.
=VALORPROC(Feriados[Feriado],Feriados[data],Calendar[data])
Em seguida, especificamos o segundo argumento, a coluna de pesquisa que contém as datas que pretendemos procurar. Especificamos a coluna Data na tabela Feriados da seguinte forma:
=VALORPROC(Feriados[Feriado],Feriados[data],Calendar[data])
Por fim, especificamos a coluna na nossa tabela de Calendar que tem as datas que pretendemos procurar na tabela de Feriados. Esta é, naturalmente, a coluna Data na tabela Calendar.
=VALORPROC(Feriados[Feriado],Feriados[data],Calendar[data])
A coluna Feriados devolverá o nome de 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 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 ocorrem no mesmo período de cada ano. Pode optar por iniciar um período em qualquer dia da semana. Tal como acontece com as datas num calendário ou ano fiscal, pode utilizar 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. Neste caso, o ano fiscal começa a 1/7.
Semana
Este valor dá-nos o número da semana a começar na primeira semana completa do ano fiscal. Neste exemplo, a primeira semana completa começa ao domingo, pelo que a primeira semana completa do primeiro ano fiscal na tabela Calendar começa efetivamente a 4/7/2010 e prolonga-se até à última semana completa na tabela Calendar. Embora este valor em si não seja assim tão útil na análise, é necessário calculá-lo para utilização noutras fórmulas de período de 28 dias.
=INT([data]-40356)/7)
Vamos analisar esta fórmula com mais cuidado.
Primeiro, criamos uma fórmula que devolve valores da coluna Data como um número inteiro, da seguinte forma:
=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/6/2010, o último domingo do ano fiscal anterior) desse valor para obter o número de dias desde o início dos dias na nossa tabela de Calendar, assim:
=INT([data]-40356)
Em seguida, divida o resultado por 7 (dias numa semana), desta forma:
=INT(([data]-40356)/7)
O resultado tem o seguinte aspeto:
Period
O período neste calendário personalizado contém 28 dias e começará sempre num domingo. Esta coluna irá devolver o número do período que começa no primeiro domingo do primeiro ano fiscal.
=INT(([Semana]+3)/4)
Vamos analisar esta fórmula com mais cuidado.
Primeiro, criamos uma fórmula que devolve um valor da coluna Semana como um número inteiro, da seguinte forma:
= INT([Semana])
Em seguida, adicione 3 a esse valor, assim:
=INT([Semana]+3)
Em seguida, divida o resultado por 4, desta forma:
=INT(([Semana]+3)/4)
O resultado tem o seguinte aspeto:
Período do Ano Fiscal
Este valor devolve o ano fiscal de um período.
=INT(([Período]+12)/13)+2008
Vamos analisar esta fórmula com mais cuidado.
Primeiro, criamos uma fórmula que devolve um valor do Ponto Final e adiciona 12:
=([Ponto]+12)
Dividimos o resultado por 13, porque existem treze períodos de 28 dias no ano fiscal:
=(([Ponto]+12)/13)
Adicionamos 2010, porque é o primeiro ano na tabela:
=(([Período]+12)/13)+2010
Por fim, utilizamos a função INT para remover uma fração do resultado e devolver um número inteiro, quando dividido por 13, da seguinte forma:
= INT(([Período]+12)/13)+2010
O resultado tem o seguinte aspeto:
Período no Ano Fiscal
Este valor devolve o número do período, 1 – 13, começando com o primeiro Período completo (que começa no domingo) de cada ano fiscal.
=SE(RESTO([Período];13), RESTO([Período];13);13)
Esta fórmula é um pouco mais complexa, pelo que vamos descrevê-la primeiro numa linguagem que compreendamos melhor. Esta fórmula indica que divide o valor de [Ponto] por 13 para obter um número de período (1-13) no ano. Se esse número for 0, é devolvido 13.
Primeiro, criamos uma fórmula que devolve o resto do valor do Período por 13. Podemos usar as funções MOD (Matemática e Trigonométrica) assim:
= RESTO([Período],13)
Isto fornece-nos maioritariamente o resultado que pretendemos, exceto quando o valor do Período é 0, porque essas datas não se inserem no primeiro ano fiscal, como nos primeiros cinco dias da nossa tabela de datas Calendar exemplo. Podemos resolver este problema com uma função SE. Caso o nosso resultado seja 0, devolvemos 13, desta forma:
= SE(RESTO([Período];13);RESTO([Período];13);13)
O resultado tem o seguinte aspeto:
Tabela Dinâmica de Exemplo
A imagem abaixo mostra uma Tabela Dinâmica com o campo SalesAmount da tabela de factos Sales em VALUES e os 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
Após criar uma tabela de data no seu Modelo de Dados, para começar a navegar pelos seus dados em Tabelas Dinâmicas e relatórios e para agregar dados com base nas colunas na sua tabela de dimensão de data, tem de criar uma relação entre a tabela de factos com os dados de transação e a tabela de data.
Uma vez que precisa de criar uma relação baseada em datas, deve certificar-se de que cria essa relação entre colunas cujos valores são do tipo de dados datetime (Date).
Para cada valor de data na tabela de factos, a coluna de pesquisa relacionada na tabela de datas tem de conter valores correspondentes. Por exemplo, uma linha (registo de transação) na tabela de factos Vendas com um valor de 15/08/2012 00:00 na coluna ChaveDeData tem de ter um valor correspondente na coluna Data relacionada na tabela de datas (denominada Calendar). Esta é uma das razões mais importantes pelas quais pretende que a coluna de datas na tabela de datas contenha um intervalo contínuo de datas que inclua quaisquer datas possíveis na sua tabela de factos.
Nota
Embora a coluna de data em cada tabela deva ser do mesmo tipo de dados (Data), o formato de cada coluna é irrelevante.
Nota
Se o Power Pivot não lhe permitir criar relações entre as duas tabelas, os campos de data poderão 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 o mesmo aspeto, mas ser armazenados de forma diferente. Leia mais sobre como trabalhar com o tempo.
Nota
Evite utilizar chaves substitutas de número inteiro nas relações. Quando importa dados a partir de uma origem de dados relacional, muitas vezes as colunas de data e hora são representadas por uma chave substituta, que é uma coluna de número inteiro utilizada para representar uma data exclusiva. No Power Pivot, deve evitar criar relações ao utilizar chaves de data/hora inteiras e, em alternativa, utilizar colunas que contenham valores exclusivos com um tipo de dados de data. Embora a utilização de chaves de substituição seja considerada uma prática recomendada em data warehouses tradicionais, as chaves de número inteiro não são necessárias no Power Pivot e podem dificultar o agrupamento de valores em tabelas dinâmicas por períodos de datas diferentes.
Se receber um erro de correspondência de Tipo quando tenta criar uma relação, é provável que a coluna na tabela de factos não seja do tipo de dados Data. Isto pode acontecer quando o Power Pivot não consegue converter automaticamente um tipo de dados que não seja de data (normalmente, um tipo de dados de texto) num tipo de dados de data. Ainda pode utilizar a coluna na sua tabela de factos, mas terá de converter os dados com uma fórmula DAX numa nova coluna calculada. Consulte Convertendo datas de tipo de dados de texto para um tipo de dados de data mais adiante no apêndice.
Relações múltiplas
Em alguns casos, poderá ser necessário criar múltiplas relações ou criar múltiplas tabelas de data. Por exemplo, se existirem vários campos de data na tabela de factos Vendas, tais como ChaveDeData, DataDeEnvio e DataDeRetorno, todos podem ter relações com o campo Data na tabela de datas Calendar, mas apenas um desses campos pode ser uma relação ativa. Neste caso, uma vez que CódigoDeData representa a data da transação e, por conseguinte, a data mais importante, esta serviria melhor como relação ativa . Os outros têm relações inativas.
A tabela dinâmica seguinte calcula o total de vendas por Ano Fiscal e Trimestre Fiscal. Uma medida denominada Total de Vendas, com a fórmula Total de Vendas:=SOMA([MontanteDasVendas]), é colocada em VALORES e os campos AnoFiscal e TrimestreFiscal da tabela de data Calendar são colocados em LINHAS.
Esta Tabela Dinâmica simples funciona corretamente porque queremos somar o total de vendas pela data da transação na Tecla de Data. A nossa medição do Total de Vendas utiliza as datas em ChaveDeData e é somada por ano fiscal e trimestre fiscal, porque existe uma relação entre ChaveDeData na tabela Vendas e a coluna Data na tabela de datas 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, as nossas agregações baseiam-se sempre na data da transação. No entanto, podemos ter múltiplas relações, apesar de apenas uma poder estar ativa, e dado que a data da transação é a mais importante, obtém a relação ativa com a tabela Calendar.
Neste caso, DataEnvio tem uma relação inativa, pelo que qualquer fórmula de medida criada para agregar dados com base em datas de envio tem de especificar a relação inativa ao utilizar a função RELAÇÃO USE .
Por exemplo, uma vez que existe 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. Utilizamos uma fórmula como esta para especificar a relação a utilizar:
Total de Vendas por Data de Envio:=CALCULAR(SOMA(Vendas[MontanteVendas]), USERELATIONSHIP(Vendas[DataEnvio], Calendar[Data]))
Esta fórmula limita-se a indicar: Calcule uma soma para SalesAmount, mas filtre utilizando a relação entre a coluna DataEnvio na tabela Vendas e a coluna Data na tabela Calendar.
Se criarmos uma Tabela Dinâmica e colocarmos o Total de Vendas por Data de Envio em VALORES, e Ano Fiscal e Trimestre Fiscal em LINHAS, vemos o mesmo Total Geral, mas todos os outros montantes de somas para o ano fiscal e o trimestre fiscal são diferentes porque se baseiam na data de envio e não na data da transação.
Tabela 
A utilização de relações inativas permite-lhe utilizar apenas uma tabela de datas, mas é necessário que qualquer medida (como o Total de Vendas por Data de Envio) faça referência à relação inativa na sua fórmula. Existe outra alternativa, ou seja, utilizar várias tabelas de data.
Várias tabelas de data
Outra forma de trabalhar com várias colunas de data na sua tabela de factos é criar múltiplas tabelas de data e criar relações ativas separadas entre as mesmas. Vamos analisar novamente o nosso exemplo de tabela Vendas. Temos três colunas com datas sobre as quais poderemos querer agregar dados:
- Uma DateKey com a data de venda de cada transação.
- Uma DataDeEnvio – com a data e hora em que os itens vendidos foram enviados para o cliente.
- Uma DataDeDevolução – com a data e hora em que um ou mais itens devolvidos foram recebidos.
Lembre-se de que o campo ChaveDeData com a data da transação é o mais importante. Faremos a maior parte das nossas agregações com base nestas datas, pelo que iremos certamente querer uma relação entre as mesmas e a coluna Data na tabela Calendar. Se não quisermos criar relações inativas entre DataDeEnvio e DataDeDevolução e o campo Data na tabela Calendar, necessitando assim de fórmulas de medidas especiais, podemos criar tabelas de datas adicionais para a data de envio e a data de devolução. Podemos então criar relações ativas entre eles.
Neste exemplo, criámos outra tabela de datas designada CalendárioDeEnvio. É claro que isto também significa criar colunas de data adicionais e, uma vez que estas colunas de data estão numa tabela de datas diferente, queremos atribuir-lhes nomes de forma a diferenciá-las das mesmas colunas na tabela Calendar. Por exemplo, criámos colunas com os nomes AnoDeEnvio, MêsDeEnvio, TrimestreDeEnvio, entre outros.
Se criarmos a nossa Tabela Dinâmica e colocarmos a nossa medida Total de Vendas em VALORES, e ShipFiscalYear e ShipFiscalQuarter em ROWS, obteremos os mesmos resultados que observámos quando criámos uma relação inativa e um campo calculado especial de Total de Vendas por Data de Envio.
Cada uma destas abordagens requer uma análise cuidadosa. Ao utilizar múltiplas relações com uma única tabela de data, poderá ter de criar medidas especiais que transitem relações inativas ao utilizar a função USERELAÇÃO. Por outro lado, criar múltiplas tabelas de data pode ser confuso numa Lista de Campos e, uma vez que tem mais tabelas no Modelo de Dados, necessitará de mais memória. Experimente o que funciona melhor para si.
Propriedade Tabela de Data
A propriedade Tabela de Data define os metadados necessários para que Time-Intelligence funções como TOTALYTD, PREVIOUSMONTH e DATESBETWEEN funcionem corretamente. Quando um cálculo é executado utilizando uma destas funções, o motor de fórmulas do Power Pivot sabe onde ir para obter as datas necessárias.
Aviso
Se esta propriedade não estiver definida, as medidas que utilizem funções DAX Time-Intelligence poderão não devolver 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 o Calendar tabela.
- No separador Estrutura , 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.
Trabalhar com o tempo
Todos os valores de data com um tipo de dados Data no Excel ou SQL Server são na realidade um número. Nesse número estão incluídos dígitos que referem uma hora. Em muitos casos, esse tempo para cada linha é meia-noite. Por exemplo, se um campo ChaveDeDataHora numa tabela de factos Vendas tiver valores como 19/10/2010 12:00:00, isto significa que os valores estão ao nível de precisão do dia. Se os valores do campo ChaveDeDataHora tiverem uma hora incluída, por exemplo, 19/10/2010 08:44:00, isto significa que os valores estão no nível de precisão mínima. Os valores também podem estar à precisão ao nível da hora ou até mesmo ao nível dos segundos. O nível de precisão no valor de hora terá um impacto significativo na forma como cria a sua tabela de datas e nas relações entre esta e a tabela de factos.
Necessita de determinar se vai agregar os dados ao nível de um dia de precisão ou a um nível de precisão temporal. Por outras palavras, pode querer utilizar colunas na sua tabela de datas, como Manhã, Tarde ou Hora, como campos de data hora nas áreas de Filtro, Coluna ou Linha, de uma Tabela Dinâmica.
Nota
Os dias são a menor unidade de tempo com a qual as funções do DAX Time Intelligence podem trabalhar. Se não precisar de trabalhar com valores de tempo, deve reduzir a precisão dos seus dados para utilizar dias como a unidade mínima.
Se pretender agregar os seus dados ao nível tempo, então a 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é cada minuto, de cada dia, para cada ano no intervalo de datas. Isto ocorre porque, para criar uma relação entre a coluna ChaveDeDataHora na tabela de factos e a coluna de data na tabela de datas, tem de ter valores correspondentes. Como você pode imaginar, se você incluir muitos anos, isso pode fazer uma tabela de data muito grande.
Na maioria dos casos, porém, você deseja agregar seus dados apenas ao dia. Por outras palavras, irá utilizar colunas como Ano, Mês, Semana ou Dia da Semana como campos nas áreas de Linha, Coluna ou Filtro de uma Tabela Dinâmica. Neste caso, a coluna de data na tabela de datas só precisa de conter uma linha para cada dia num ano, como descrevemos anteriormente.
Se a sua coluna de data incluir um nível de precisão de tempo, mas quiser agregar apenas a um nível de dia, para criar a relação entre a tabela de factos e a tabela de datas, poderá ter de modificar a sua tabela de factos ao criar uma nova coluna que trunca os valores na coluna de data para um valor de dia. Por outras palavras, converta um valor como 19/10/2010 08:44:00 para19/10/2010 00:00:00. Em seguida, pode criar a relação entre esta nova coluna e a coluna de data na tabela de datas, porque os valores correspondem.
Vejamos um exemplo. Esta imagem mostra uma coluna ChaveDeDataHora na tabela de factos Vendas. Todas as agregações de dados nesta tabela só têm de estar ao nível do dia, ao utilizar colunas na Calendar tabela de datas, como Ano, Mês, Trimestre, etc. A hora incluída no valor não é relevante, apenas a data real.
Uma vez que não precisamos de analisar estes dados até ao nível temporal, não precisamos que a coluna Data na tabela de datas Calendar inclua uma linha para cada hora e cada minuto de cada dia em cada ano. Portanto, a coluna Data na nossa tabela de datas tem o seguinte aspeto:
Para criar uma relação entre a coluna ChaveDeDataHora na tabela Vendas e a coluna Data na tabela Calendar, podemos criar uma nova coluna calculada na tabela de factos Vendas e utilizar a função TRUNCAR para truncar o valor de data e hora na coluna ChaveDeDataHora num valor de data que corresponda aos valores na coluna Data na tabela Calendar. A nossa fórmula tem o seguinte aspeto:
=TRUNCAR([ChaveDataHora],0)
Isto dá-nos uma nova coluna (com o nome de ChaveDeData) com a data da coluna ChaveDeDataHora e uma hora de 12:00:00 para cada linha:
Agora podemos criar uma relação entre esta nova coluna (ChaveDeData) e a coluna Data na tabela Calendar.
Do mesmo modo, podemos criar uma coluna calculada na tabela Vendas, que reduz a precisão de tempo na coluna ChaveDeDataHora para o nível de precisão de hora. Neste caso, a função TRUNCAR não funcionará, mas ainda podemos utilizar outras funções DAX de Data e Hora para extrair e concatenar novamente um novo valor para um nível de precisão de uma hora. Podemos utilizar uma fórmula como esta:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
A nossa nova coluna tem o seguinte aspeto:
Desde que a nossa coluna Data na tabela de datas tenha valores com o nível de precisão de hora, podemos criar uma relação entre eles.
Tornar as datas mais utilizáveis
Muitas das colunas de data que cria na sua tabela de datas são necessárias para outros campos, mas não são tão úteis na análise. Por exemplo, o campo ChaveDeData na tabela Vendas a que fizemos referência e mostrámos ao longo deste artigo é importante porque, para cada transação, essa transação é registada como ocorrendo numa data e hora específicas. Mas, do ponto de vista da análise e da elaboração de relatórios, não é assim tão útil, porque não podemos utilizá-la como um campo de linha, coluna ou filtro numa Tabela Dinâmica ou relatório.
Do mesmo modo, no nosso exemplo, a coluna Data na tabela Calendar é muito útil, mas não pode utilizá-la como uma dimensão numa Tabela Dinâmica.
Para manter as tabelas e as colunas nas mesmas o mais úteis possível e para tornar as listas de campos de relatório da Tabela Dinâmica ou do Power View mais fáceis de navegar, é 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 da tabela Calendar, mas não pode utilizar as colunas de Data e Feriados na tabela Feriados como campos numa Tabela Dinâmica. Mais uma vez, para facilitar a navegação nas Listas de Campos, pode ocultar a tabela Feriados inteira.
Outro aspecto importante de trabalhar com datas são as convenções de nomenclatura. Pode atribuir o nome que quiser às tabelas e colunas no Power Pivot. Lembre-se, especialmente se pretender partilhar o seu livro com outros utilizadores, que 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 data 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 você precisa filtrar em um determinado intervalo de datas exclusivas. Saiba mais em Medidas nas Funções Power Pivot e 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 poderá conter datas de tipo de dados de texto. Ou seja, uma data que aparece como 2012-12-04T11:47:09 não é de todo uma data, ou pelo menos não é o tipo de data que o Power Pivot consegue compreender. Na verdade, é 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 data, 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 um tipo de dados de texto para um tipo de dados de data, o Power Pivot consegue interpretar as datas e convertê-las automaticamente num tipo de dados de data verdadeira. Se o Power Pivot não conseguir efetuar uma conversão de tipo de dados, irá obter um erro de correspondência de tipo.
No entanto, ainda pode converter as datas num tipo de dados de data verdadeira. 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, em seguida, concatená-la novamente de uma forma que o Power Pivot possa ler como uma data verdadeira.
Neste exemplo, importámos uma tabela de factos com o nome Vendas para o Power Pivot. Contém uma coluna designada DataHora. Os valores são apresentados assim:
Se observarmos o Tipo de Dados no separador Base do grupo Formatação do Power Pivot, vemos que é um tipo de dados de Texto.
Não conseguimos criar uma relação entre a coluna DataHora e a coluna Data 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 a data. Continuamos a poder utilizar esta coluna, mas para que seja um tipo de dados Data verdadeira, temos de criar uma nova coluna que analise o texto e o recrie, num valor que o Power Pivot pode transformar num tipo de dados Data.
Lembre-se, da seção Trabalhando com o tempo no início deste artigo; A menos que seja necessário que a sua análise tenha 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ária. 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 na coluna DataHora num tipo de dados de data e remover o nível de precisão de hora com a seguinte fórmula:
=DATA(ESQUERDA([DateTime];4), SEG.TEXTO([DateTime];6;2), SEG.TEXTO([DataHora];9;2))
Isto dá-nos uma nova coluna (neste caso, com o nome Data). O Power Pivot até deteta os valores como datas e define o tipo de dados automaticamente para Data.
Se quisermos preservar o nível de tempo de precisão, simplesmente estendemos a fórmula para incluir as horas, minutos e segundos.
=DATA(ESQUERDA([DateTime],4), SEG.TEXTO([DataHora];6;2), SEG.TEXTO([DateTime];9;2)) +
TEMPO(SEG.TEXTO([DateTime];12;2), SEG.TEXTO([DataHora];15;2), SEG.TEXTO([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 numa data.
Recursos adicionais
Guia de Introdução: Noções Básicas sobre a linguagem DAX em 30 Minutos