Entender e criar tabelas de data no Power Pivot no Excel

Observação: Desejamos fornecer o conteúdo da Ajuda mais recente no seu idioma da forma mais rápida possível. Esta página foi traduzida de forma automatizada e pode conter imprecisões ou erros gramaticais. Nosso objetivo é que este conteúdo seja útil para você. No final da página, deixe sua opinião se estas informações foram úteis para você. Aqui está o artigo em inglês para facilitar a referência.

As tabelas de data no Power Pivot são essenciais para navegar e calcular os 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 data é importante para navegar e calcular os dados por data e hora.

  • 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 data.

  • 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 para o Power pivot. No enTanto, é importante já ter uma boa compreensão 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 de inteligência de tempo do Dax em fórmulas de medição. Para obter mais informações sobre como criar medidas com funções DAX de inteligência de tempo, consulte inteligência de tempo 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 datas

Adicionando uma tabela de data ao modelo de dados

Importar de um banco de dados relacional

Criar uma tabela de data no Excel

Como: criar uma tabela de data no Excel e copiá-la para o modelo de dados

Adicionar novas colunas de data à tabela de data

Funções DAX de data e hora

Exemplos de fórmulas para um ano civil

Ano

Mês

Mestra

Nome do mês

Exemplos de fórmulas para um ano fiscal

Ano fiscal

Mês fiscal

Trimestre fiscal

Feriados ou datas especiais

Calendário personalizado-13 4-períodos da semana

Semana

Período

Ano fiscal do período

Período em FiscalYear

Relacionamentos

Várias relações

Relações inativas

Tabelas de várias datas

Propriedade de tabela de data

Trabalhando com tempo

Colocando datas mais utilizáveis

Apêndice

Converter tipos de dados de texto em um tipo de dados de data

Recursos adicionais

Noções básicas sobre tabelas de datas

Praticamente toda a análise de dados envolve a navegação e a comparação de dados ao longo de datas e horas. Por exemplo, você pode querer somar valores de vendas para o trimestre fiscal anterior e comparar esses totais com outros trimestres, ou pode querer calcular um saldo de fechamento de fim de mês para uma conta. Em cada um desses casos, você está usando datas como uma maneira de agrupar e agregar transações de vendas ou saldos para um período específico no tempo.

Relatório do Power View

Tabela dinâmica de vendas totais por trimestre fiscal

Uma tabela de data pode conter várias 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 criar e filtrar seus dados em tabelas dinâmicas ou relatórios do Power View.

Lista de campos do Power View

Lista de Campo do Power View

Para colunas de data, como ano, mês e trimestre, para incluir todas as datas dentro de seu respectivo intervalo, a tabela de data deve ter pelo menos uma coluna com um conjunto contíguo de datas. Ou seja, essa coluna deve ter uma linha para todos os dias para cada ano incluído na tabela de data.

Por exemplo, se os dados que você deseja procurar tiverem datas de 1º de fevereiro de 2010 até 30 de novembro de 2012 e relatar um ano civil, você desejará uma tabela de data com pelo menos um intervalo de datas de 1 a 31 de dezembro a 2012. Cada ano da tabela de data deve conter todos os dias de cada ano. Se você estiver atualizando regularmente seus dados com dados mais recentes, talvez queira executar a data de término em um ano ou duas, para que você não precise atualizar a tabela de data Enquanto o tempo leva.

Tabela de data com um conjunto de datas contíguo

Tabela de data com datas contíguas

Se você relatar um ano fiscal, poderá criar uma tabela de data com um conjunto de datas contíguo para cada ano fiscal. Por exemplo, se o ano fiscal começar em 1º de março, e você tiver dados de anos fiscais 2010 até a data atual (por exemplo, em AF 2013), poderá criar uma tabela de data que comece no 3/1/2009 e inclua pelo menos todos os dias em cada ano fiscal até a última data no ano fiscal 2013.

Se você for relatar o ano civil e o ano fiscal, você não precisará criar tabelas de data separadas. Uma tabela de data única pode incluir colunas para um ano civil, ano fiscal e até mesmo um calendário do período de 13 4 semanas. A importante é que a sua tabela de data contém um conjunto contíguo de datas para todos os anos incluídos.

Adicionando uma tabela de data ao modelo de dados

Há várias maneiras de adicionar uma tabela de data ao seu modelo de dados:

  • Importar de um banco de dados relacional ou outra fonte de dados.

  • Crie uma tabela de data no Excel e copie ou vincule uma nova tabela no Power pivot.

  • Importar do Microsoft Azure Marketplace.

Vamos examinar cada um desses mais atentamente.

Importar de um banco de dados relacional

Se você importar alguns ou todos os seus dados de um data warehouse ou outro tipo de banco de dados relacional, é provável que já exista uma tabela de data e relações entre ele e o restante dos dados que você está importando. As datas e os formatos provavelmente corresponderão às datas dos seus dados de fatos, e as datas provavelmente começarão bem no passado e vão muito para o futuro. A tabela de data 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

Caixa de diálogo do Assistente de importação de tabela

Na maioria dos casos, você não precisará criar mais colunas, como ano fiscal, semana, nome do mês, etc. porque elas já existem na tabela importada. No enTanto, em alguns casos, depois de ter a tabela de data importada para o seu modelo de dados, talvez seja necessário criar colunas de data adicionais, dependendo de uma determinada necessidade de relatórios. Felizmente, é fácil usar DAX. Você aprenderá mais sobre a criação de campos de tabela de data mais tarde. Cada ambiente é diferente. Se você não tiver certeza de que suas fontes de dados têm uma tabela de data ou calendário relacionada, fale com o administrador do banco de dados.

Criar uma tabela de data no Excel

Você pode criar uma tabela de data no Excel e, em seguida, copiá-la para uma nova tabela no modelo de dados. Isso é muito fácil de fazer e oferece muita flexibilidade.

Quando você cria uma tabela de data no Excel, você começa com uma única coluna com um intervalo de datas contíguo. 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 depois de copiar a tabela para o modelo de dados, você pode criá-las como colunas calculadas. A criação de colunas de data adicionais no Power Pivot é descrita na seção adicionar novas colunas à tabela de data mais adiante neste artigo.

Como: criar uma tabela de data no Excel e copiá-la para o modelo de dados

  1. 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. Geralmente, isso seráalgo como Date, DateTime ou DateKey.

  2. Na célula a2, digite uma data inicial. Por exemplo, 1/1/2010.

  3. 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, 12/31/2016.

    Coluna de data no Excel

  4. Selecione todas as linhas na coluna Data (incluindo o nome do cabeçalho na célula a1).

  5. No grupo estilos , clique em Formatar como tabelae selecione um estilo.

  6. Na caixa de diálogo Formatar como Tabela, clique em OK.

    Coluna de data no Power Pivot

  7. Copie todas as linhas, incluindo o cabeçalho.

  8. No Power pivot, na guia página inicial , clique em colar.

  9. Em visualização de colagem , > nome da tabela digite um nome como Data ou calendário. Deixe a opção usar primeira linha como cabeçalhosde coluna marcada e clique em OK.

    Visualização de Colagem

    A nova tabela de data (chamada calendário neste exemplo) no Power pivot tem a seguinte aparência:

    Tabela de data no Power Pivot

    Observação: Você também pode criar uma tabela vinculada usando Adicionar ao modelo de dados. No enTanto, isso torna a pasta de trabalho desnecessariamente grande porque a pasta de trabalho tem duas versões da tabela de data; um no Excel e um no Power pivot.

Observação: O nome Data é uma palavra-chave no Power pivot. Se você nomear a tabela criada na data do Power pivot, será necessário colocar o nome da tabela entre aspas simples em qualquer fórmula DAX que fazem referência a ela em um argumento. Todas as imagens de exemplo e fórmulas neste artigo referem-se a uma tabela de data criada no Power pivot chamado calendário.

Agora você tem uma tabela de data no seu modelo de dados. Você pode adicionar novas colunas de data, como ano, mês, etc. usando DAX.

Adicionar novas colunas de data à tabela de data

Uma tabela de data com uma única coluna de data que tem uma linha para todos os dias 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 data. Mas essa coluna de data única com uma linha para todos os dias não é útil ao analisar datas em um relatório de tabela dinâmica ou Power View. Você deseja que a tabela de data inclua colunas que o ajudem a agregar seus dados para um intervalo ou grupo de datas. Por exemplo, você pode querer somar os valores das vendas por mês ou trimestre, ou pode criar uma medida que calcule o crescimento ano a ano. Em cada um desses casos, sua tabela de data precisa ter 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, talvez ela já inclua os diferentes tipos de colunas de data desejadas. 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, a criação de novas colunas de data no Power Pivot é muito fácil com funções de data e hora em Dax.

Dica: Se você ainda não trabalhou com DAX, um ótimo lugar para começar a aprender é com o QuickStart: Aprenda os fundamentos de Dax em 30 minutos em Office.com.

Funções DAX de data e hora

Se você já trabalhou com funções de data e hora nas fórmulas do Excel, provavelmente estará familiarizado com as funções de data e hora. Embora essas funções sejam semelhantes aos seus correspondentes no Excel, há algumas diferenças importantes:

  • Funções DAX data e hora usam um tipo de dados DateTime.

  • Eles podem pegar valores de uma coluna como um argumento.

  • Elas podem ser usadas para retornar e/ou manipular valores de data.

Essas funções geralmente são usadas ao criar colunas de data personalizadas em uma tabela de data, para que sejam importantes entender. Usaremos várias funções para criar colunas para ano, trimestre, FiscalMonth e assim por diante.

Observação: 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 inteligência de tempo no Power pivot no Excel 2013.

O DAX inclui as seguintes funções de data e hora:

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 Format , para obter mais informações sobre outras funções de DAX, consulte a seção recursos adicionais mais adiante neste artigo.

Exemplos de fórmulas para um ano civil

Os exemplos a seguir descrevem as fórmulas usadas para criar colunas adicionais em uma tabela de data chamada calendário. Uma coluna, data nomeada, já existe e contém um intervalo de datas contíguo entre 1/1/2010 e 12/31/2016.

Ano

= ANO ([data])

Nessa fórmula, a função ano retorna o ano a partir do valor na coluna Data. Como o valor na coluna data é do tipo de dados DateTime, a função ano sabe como retornar o ano a partir dele.

Coluna do ano

Mês

= MÊS ([data])

Nessa fórmula, muito parecida com a função ano, podemos simplesmente usar a função mês para retornar um valor de mês da coluna Data.

Coluna do mês

Mestra

= INT (([mês] + 2)/3)

Nessa fórmula, usamos a função int para retornar um valor de data como um inteiro. O argumento que especificamos para a função INT é o valor da coluna mês, adiciona 2 e depois divide isso por 3 para obter o nosso trimestre, de 1 a 4.

Coluna do trimestre

Nome do mês

= FORMAT ([data], "MMMM")

Nesta fórmula, para obter o nome do mês, usamos a função formato para converter um valor numérico da coluna de 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, então usamos "MMMM". Nosso resultado tem a seguinte aparência:

Coluna de nome do mês

Se quisermos retornar o nome do mês abreviado para três letras, usaremos "Mmm" no argumento format.

Dia da semana

= FORMAT ([data], "ddd")

Nesta fórmula, usamos a função FORMAT para obter o nome do dia. Como queremos apenas um nome de dia abreviado, especificamos "ddd" no argumento formato.

Coluna do dia da semana
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 valores e ano e trimestre da tabela de dimensões do calendário em linhas. SalesAmount é agregado para o contexto ano e trimestre.

Exemplo de Tabela Dinâmica

Exemplos de fórmulas para um ano fiscal

Ano fiscal

= SE ([mês] < = 6, [ano], [ano] + 1)

Neste exemplo, o ano fiscal começa em 1º de julho.

Não há função que possa extrair um ano fiscal de um valor de data porque as datas de início e de término de um ano fiscal são muitas vezes diferentes das de um ano civil. Para obter o ano fiscal, usamos primeiro uma função se para testar se o valor de month é menor ou igual a 6. No segundo argumento, se o valor de month for menor ou igual a 6, retorne o valor da coluna ano. Caso contrário, retorne o valor do ano e adicione 1.

Coluna de ano fiscal

Outra maneira de especificar um valor de mês de fim de ano fiscal é criar uma medida que simplesmente especifica o mês. Por exemplo, FYE: = 6. Em seguida, você pode fazer referência ao nome da medida no lugar do número do mês. Por exemplo, = se ([mês] < = [FYE], [ano], [ano] + 1). Isso proporciona mais flexibilidade ao fazer referência ao mês final do ano fiscal em várias fórmulas diferentes.

Mês fiscal

= SE ([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, demoram 6 e adicionamos o valor do mês, caso contrário, subtraia 6 do valor de [mês].

Coluna de mês fiscal

Trimestre fiscal

= INT (([FiscalMonth] + 2)/3)

A fórmula que usamos para FiscalQuarter é muito parecida com a do trimestre do nosso ano civil. A única diferença é especificar [FiscalMonth] em vez de [mês].

Coluna de trimestre fiscal

Feriados ou datas especiais

Pode ser que você queira incluir uma coluna de data que indica que determinadas datas são feriados ou outra data especial. Por exemplo, você pode querer somar totais de vendas para o novo dia de ano adicionando um campo feriados a uma tabela dinâmica, como uma segmentação de subsegmentação ou filtro. Em outros casos, talvez você queira excluir essas datas de outras colunas de data ou de uma medida.

Incluir feriados ou dias especiais é muito simples. Você pode criar uma tabela no Excel que tenha as datas que você 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 de calendário. Todas as fórmulas que fazem referência a ela podem usar a função LookupValue para retornar valores.

Veja a seguir um exemplo de uma tabela criada no Excel que inclui feriados a serem adicionados à tabela de data:

Data

Feriado

1/1/2010

Ano novo

11/25/2010

Graças

12/25/2010

Natal

01.01.11

Ano novo

11/24/2011

Graças

12/25/2011

Natal

01.01.12

Ano novo

22.11.12

Graças

12/25/2012

Natal

1/1/2013

Ano novo

11/28/2013

Graças

12/25/2013

Natal

11/27/2014

Graças

12/25/2014

Natal

01/01/2014

Ano novo

11/27/2014

Graças

12/25/2014

Natal

1/1/2015

Ano novo

11/26/2014

Graças

12/25/2015

Natal

01.01.16

Ano novo

11/24/2016

Graças

12/25/2016

Natal

Na tabela data, criamos uma coluna chamada feriado e usamos uma fórmula como esta:

= LOOKUPvalue (feriados [feriado], feriados [data], calendário [data])

Vamos dar uma olhada mais atenta a essa fórmula.

Usamos a função LOOKUPvalue para obter valores da coluna feriados na tabela feriados. No primeiro argumento, especificamos a coluna em que o valor do resultado será. Especificamos a coluna feriados na tabela feriados porque esse é o valor que queremos retornar.

= LOOKUPvalue (feriados [feriado], feriados [data], calendário [data])

Em seguida, especificamos o segundo argumento, a coluna de pesquisa que tem as datas que queremos Pesquisar. Especificamos a coluna Data na tabela feriados , como esta:

= LOOKUPvalue (feriados [feriado],feriados [data], calendário [data])

Por fim, especificamos a coluna na tabela de calendário que contém as datas para as quais queremos Pesquisar na tabela feriados . Este é o curso é a coluna Data na tabela de calendário .

= LOOKUPvalue (feriados [feriado], feriados [data],calendário [data])

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.

Tabela Feriado

Calendário personalizado-13 4-períodos da semana

Algumas organizações, como serviço de varejo ou comida, geralmente se reportam em períodos diferentes, como períodos de 13 4 semanas. Com um calendário do período de 13 4 semanas, cada período é de 28 dias; Portanto, cada período contém quatro segunda-feira, quatro terças, quatro feiras e assim por diante. Cada período contém o mesmo número de dias e, em geral, os feriados ficarão dentro do 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 mais colunas com datas personalizadas.

Nos exemplos a seguir, 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 do primeiro ano fiscal na tabela de calendário realmente começa no 7/4/2010 e continua pela última semana completa na tabela de calendário. 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)

Vamos dar uma olhada mais atenta a essa fórmula.

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.

Coluna da semana

Agora, subtraia 40356 (que é o inteiro de 6/27/2010, o último domingo do ano fiscal anterior) desse valor para obter o número de dias desde o início dos dias na tabela do calendário, assim:

= INT ([data]-40356)

Em seguida, divida o resultado por 7 (dias em uma semana), como este:

= INT (([data]-40356)/7)

O resultado tem a seguinte aparência:

Coluna da semana

Período

O período neste calendário personalizado contém 28 dias e ele sempre será iniciado em um domingo. Esta coluna retornará o número do período começando pelo primeiro domingo do primeiro ano fiscal.

= INT (([semana] + 3)/4)

Vamos dar uma olhada mais atenta a essa fórmula.

Primeiro, criamos uma fórmula que retorna um valor da coluna Week como um inteiro, assim:

=Int ([semana])

Em seguida, adicione 3 a esse valor, como este:

= INT ([semana]+ 3)

Em seguida, divida o resultado por 4, como este:

= INT (([semana] + 3)/4)

O resultado tem a seguinte aparência:

Coluna Período

Ano fiscal do período

Esse valor retorna o ano fiscal de um período.

= INT (([período] + 12)/13) + 2008

Vamos dar uma olhada mais atenta a essa fórmula.

Primeiro, criamos uma fórmula que retorna um valor de período e adiciona 12:

= ([Período] + 12)

Dividimos o resultado por 13, porque há 13 28 períodos de dia no ano fiscal:

= (([Período] + 12)/13)

Adicionamos 2010, porque esse é o primeiro ano da tabela:

= (([Período] + 12)/13)+ 2010

Por fim, usamos a função INT para remover qualquer fração do resultado e retornamos um número inteiro, quando dividido por 13, assim:

=Int(([período] + 12)/13)+2010

O resultado tem a seguinte aparência:

Coluna Ano Fiscal do período

Período em FiscalYear

Esse valor retorna o número do período de 1 a 13, começando com o primeiro período completo (a partir do domingo) em cada ano fiscal.

= SE (MOD ([período], 13), MOD ([período], 13), 13)

Essa fórmula é um pouco mais complexa, portanto, descreveremos primeiro em uma linguagem que melhor entendemos. Essa fórmula diz, 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 resto do valor do período por 13. Podemos usar as funções mod (matemática e trigonométrica) da seguinte forma:

=Mod ([período], 13)

Isso, na maioria dos casos, nos dá o resultado que queremos, exceto onde o valor de período for 0 porque essas datas não estão no primeiro ano fiscal, como nos primeiros cinco dias da tabela de exemplo de data do calendário. Nós podemos cuidar disso com uma função se. Caso o resultado seja 0, retornamos 13, como este:

=Se(mod ([período], 13), mod ([período], 13), 13)

O resultado tem a seguinte aparência:

Coluna Período em Ano Fiscal

Exemplo de Tabela Dinâmica

A imagem abaixo mostra uma tabela dinâmica com o campo SalesAmount da tabela de fatos de vendas em valores, e os campos PeriodFiscalYear e PeriodInFiscalYear da tabela de dimensão de data do calendário em linhas. SalesAmount é agregado para o contexto por ano fiscal e período de 28 dias no ano fiscal.

Exemplo de Tabela Dinâmica para o ano fiscal

Relacionamentos

Depois de criar uma tabela de data no seu modelo de dados, para começar a navegar pelos dados em tabelas dinâmicas e relatórios e agregar dados com base nas colunas na tabela de dimensão de data, você precisará criar uma relação entre a tabela de fatos com seus dados de transação e a tabela Data.

Como você precisa criar uma relação com base em datas, você desejará certificar-se de criar essa relação entre as 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 data deve conter valores correspondentes. Por exemplo, uma linha (registro de transação) na tabela de fatos de vendas com um valor de 8/15/2012 12:00 AM na coluna DateKey deve ter um valor correspondente na coluna de data relacionada na tabela Data (calendário nomeado). Esta é uma das razões mais importantes para a coluna de data na tabela de data ter um intervalo de datas contíguo que inclua qualquer data possível na tabela de fatos.

Criar relações no Modo de Exibição de Diagrama

Observação: Embora a coluna data em cada tabela deva ser do mesmo tipo de dados (Date), 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 parecer iguais, mas são armazenados de forma diferente. Leia mais sobre como trabalhar com o tempo.

Observação: Evite o uso de chaves substitutas de inteiros em relações. Quando você importa dados de uma fonte de dados relacional, geralmente as colunas 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 as chaves de data/hora inteira e, em vez disso, usar colunas que contêm valores exclusivos com um tipo de dados de data. Embora o uso de chaves alternativas seja considerado como prática recomendada em armazéns de dados 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 tipo incompatibilidade ao tentar criar uma relação, é provável que a coluna na tabela de fatos não seja do tipo de dados de data. Isso pode acontecer quando o Power pivot não pode converter automaticamente um tipo de dados que não seja uma 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 conversão de tipos de dados de texto em um tipo de dados de data posteriormente no apêndice.

Várias relações

Em alguns casos, talvez seja necessário criar várias relações ou criar várias tabelas de data. Por exemplo, se houver vários campos de data na tabela de fatos de vendas, como DateKey, ShipDate e ReturnDate, todos poderão ter relações com o campo de data na tabela de data do calendário, mas somente 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 melhor serviria como a relação ativa . Os outros têm relações inativas.

A tabela dinâmica a seguir calcula as vendas totais por ano fiscal e trimestre fiscal. Uma medida chamada total Sales, com a fórmula Total Sales: = Sum ([SalesAmount]), é colocada em valores, e os campos FiscalYear e FiscalQuarter da tabela Date do calendário são colocados em linhas.

Tabela dinâmica do total de vendas por trimestre fiscal Lista de Campo da Tabela Dinâmica

Esta tabela dinâmica direcionada funciona corretamente porque queremos somar nosso total de vendas pelaData da transaçãono DateKey. Nossa medida de vendas total usa as datas no DateKey e é somada por ano fiscal e trimestre fiscal porque há uma relação entre DateKey na tabela Sales e a coluna data na tabela de data do calendário.

Relações inativas

Mas e se quiséssemos somar o total de vendas não pela data da transação, mas pela data da remessa? Precisamos de uma relação entre a coluna ShipDate na tabela Sales e a coluna Date na tabela Calendar. Se não criarmos essa relação, nossas agregações sempre serão baseadas na data da transação. No enTanto, podemos ter várias relações, apesar de apenas um poder estar ativo, e porque a data da transação é o mais importante, ela obtém a relação ativa com a tabela de 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 remessa 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 Sales e a coluna Date na tabela de calendário, podemos criar uma medida que soma as vendas totais por data de envio. Usamos uma fórmula como esta para especificar a relação a ser usada:

Total de vendas por data de envio: = CALCULATE (SUM (Sales [SalesAmount]), USERELATIONSHIP (vendas [ShipDate], calendário [data]))

Essa fórmula simplesmente informa: 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 as vendas toTais por data de remessa em valores, ano fiscal e trimestre fiscal em linhas, veremos o mesmo total geral, mas todos os outros valores de soma para ano fiscal e trimestre fiscal são diferentes porque são baseados na data de envio e Não a data da transação.

Tabela dinâmica de vendas totais por data de envio Lista de Campo da Tabela Dinâmica

O uso de relações inativas permite que você use apenas uma tabela de data, mas requer que qualquer medida (como o total de vendas por data de remessa) faça referência à relação inativa em sua fórmula. Há outra alternativa, ou seja, use várias tabelas de data.

Tabelas de várias datas

Outra maneira de trabalhar com várias colunas de data na tabela de fatos é criar várias tabelas de data e criar relações ativas separadas entre elas. Vamos dar uma olhada em nosso exemplo de tabela de vendas novamente. Temos três colunas com datas para as quais desejamos agregar dados:

  • Um DateKey com a data de venda de 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 foram recebidos.

Lembre-se de que o campo DateKey com a data da transação é mais importante. Faremos a maior parte das nossas agregações com base nessas datas, portanto, com certeza, vamos querer uma relação entre ela e a coluna data na tabela de calendário. Se não quisermos criar relações inativas entre ShipDate e ReturnDate e o campo Date na tabela de calendário, exigindo fórmulas de medida especiais, podemos criar tabelas de data adicionais para a data de remessa e a data de retorno. Em seguida, podemos criar relações ativas entre elas.

Relações com várias tabelas de data no Modo de Exibição de Diagrama

Neste exemplo, criamos outra tabela de data chamada ShipCalendar. Isso, obviamente, 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 forma que diferencie-as das mesmas colunas na tabela de calendário. Por exemplo, criamos colunas chamadas ShipYear, ShipMonth, ShipQuarter e assim por diante.

Se criarmos nossa tabela dinâmica e colocarmos a medida de vendas toTais em valores, e ShipFiscalYear e ShipFiscalQuarter em linhas, veremos os mesmos resultados que vimos quando criamos uma relação inativa e um campo calculado de vendas de total especial por data de remessa.

Tabela dinâmica de vendas totais por data de envio com calendário de envio lista de campos de tabela dinâmica

Cada uma dessas abordagens requer consideração cuidadosa. Ao usar várias relações com uma tabela de data única, talvez seja necessário criar medidas especiais que transitam de relações inativas usando a função USERELATIONSHIP. Por outro lado, a criação de várias tabelas de datas pode ser confusa em uma lista de campos e, como você tem mais tabelas no modelo de dados, será necessária mais memória. Experimente com o que funciona melhor para você.

Propriedade de tabela de data

A propriedade tabela de data define os metadados necessários para funções de inteligência de tempo, como TOTALYTD, PREVIOUSMONTH e DATESBETWEEN, para funcionar 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 de que precisa.

Aviso: Se essa propriedade não estiver definida, as medidas que usam funções DAX de inteligência de tempo podem não retornar resultados corretos.

Quando você define a propriedade da tabela de data, especifica uma tabela de data e uma coluna de data do tipo de dados data (DateTime).

Caixa de diálogo Marcar Como Tabela de Data

Como: definir a propriedade da tabela de data

  1. Na janela do PowerPivot, selecione a tabela de calendário .

  2. Na guia design , clique em Marcar como tabela de data.

  3. Na caixa de diálogo marcar como tabela de data, selecione uma coluna com valores exclusivos e o tipo de dados Data.

Trabalhando com tempo

Todos os valores de data com um tipo de dados de data no Excel ou SQL Server são na verdade um número. Incluído nesse número há dígitos que fazem referência a um tempo. Em muitos casos, esse tempo para cada linha e cada linha é meia-noite. Por exemplo, se um campo DateTimeKey em uma tabela de fatos de vendas tem valores como 10/19/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 um tempo incluído, por exemplo, 10/19/2010 8:44:00 AM, isso significará que os valores estão para o nível de precisão de minutos. Os valores também podem ser a precisão do nível da hora ou, até segundos, o nível de precisão. O nível de precisão no valor de tempo terá um impacto significativo sobre como criar a tabela de data e as relações entre ela e a tabela de fatos.

Você precisa determinar se irá agregar seus dados a um nível de precisão de dia ou a um nível de tempo de precisão. Em outras palavras, você pode querer usar colunas na sua tabela de data, como campos de manhã, tarde ou hora como data de hora em uma linha da tabela dinâmica, coluna ou áreas de filtro.

Observação: Dias são a menor unidade de tempo para a qual as funções de inteligência de tempo DAX podem funcionar. Se você não precisar trabalhar com valores de tempo, você deve reduzir a precisão dos seus dados para usar os dias como a unidade mínima.

Se você pretende agregar seus dados ao nível de tempo, a tabela de data precisará de uma coluna de data com o tempo incluído. Na verdade, será necessária uma coluna de data com uma linha de cada hora, ou talvez até 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 incluir muitos anos, isso poderá fazer uma tabela de data muito grande.

Na maioria dos casos, você só deseja agregar seus dados ao dia. Em outras palavras, você usará colunas como ano, mês, semana ou dia da semana como campos nas áreas de linhas, colunas ou filtros de uma tabela dinâmica. Nesse caso, a coluna data na tabela de data precisa conter apenas uma linha para cada dia em um ano, conforme descrito anteriormente.

Se a coluna de data incluir um nível de tempo de precisão, mas você será agregado 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 a tabela de fatos criando uma nova coluna que trunca os valores na data c olumn para um valor de dia. Em outras palavras, converta um valor como 10/19/2010 8:44:00AM para 10/19/2010 12:00:00 am. Em seguida, você pode criar a relação entre esta 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 Sales. Todas as agregações dos dados nesta tabela só precisam ser do nível do dia, usando colunas na tabela de data do calendário, como ano, mês, trimestre, etc. O tempo incluído no valor não é relevante, apenas a data real.

Coluna DateTimeKey

Como não precisamos analisar esses dados no nível de tempo, não precisamos da coluna data na tabela de data do calendário para incluir uma linha de cada hora e todos os minutos de cada dia em cada ano. Portanto, a coluna data na tabela de data tem a seguinte aparência:

Coluna de data no Power Pivot

Para criar uma relação entre a coluna DateTimeKey na tabela Sales e a coluna Date na tabela de calendário, 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 no DateTimeKey coluna em um valor de data que corresponda aos valores na coluna data na tabela de calendário. Nossa fórmula tem a seguinte aparência:

= TRUNC ([DateTimeKey]; 0)

Isso nos dá uma nova coluna (denominamos DateKey) com a data da coluna DateTimeKey e uma hora de 12:00:00 AM para cada linha:

Coluna DateKey

Agora podemos criar uma relação entre esta nova coluna (DateKey) e a coluna data na tabela de calendário.

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 de hora. Nesse caso, a função TRUNCAr não funcionará, mas ainda podemos usar outras funções de data e hora DAX para extrair e concatenar novamente um novo valor para um nível de precisão de hora. Podemos usar uma fórmula como esta:

= Data (ano ([DateTimeKey]), mês ([DateTimeKey]), dia ([DateTimeKey])) + hora (hora ([DateTimeKey]), 0, 0)

Nossa nova coluna tem a seguinte aparência:

Coluna DateTimeKey

Desde que a nossa coluna de data na tabela de data tenha valores para o nível de precisão de hora, podemos criar uma relação entre elas.

Colocando datas mais utilizáveis

Muitas das colunas de data que você cria na tabela de data são necessárias para outros campos, mas não são tão úteis na análise. Por exemplo, o campo DateKey na tabela Sales referenciamos e mostrado em todo este artigo é importante porque para cada transação, essa transação é gravada como ocorre em uma data e hora específicas. Mas de um ponto de vista de análise e relatório, não é tão útil que não podemos usá-lo como um campo de linha, coluna ou filtro em uma tabela ou relatório dinâmico.

Da mesma forma, em nosso exemplo, a coluna data na tabela de calendário é muito útil, 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 o mais úteis possível e para facilitar a navegação em listas de campos de relatório de tabela dinâmica ou Power View, é importante ocultar as colunas desnecessárias das ferramentas de cliente. Você também pode querer ocultar algumas tabelas também. A tabela feriados mostrada anteriormente contém datas de feriado importantes para determinadas colunas na tabela de calendário, mas você não pode usar as colunas data e feriado na própria tabela feriados como campos em uma tabela dinâmica. Aqui novamente, para facilitar a navegação nas listas de campos, você pode ocultar a tabela feriados inteira.

Outro aspecto importante do trabalho com datas é a Convenção de nomenclatura. Você pode nomear tabelas e colunas no Power pivot o que quiser. Mas lembre-se, especialmente se você estiver compartilhando 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 nas 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, você pode começar a criar medidas que ajudarão você a tirar o máximo proveito dos seus dados. Alguns podem ser tão simples quanto somar os totais de vendas do ano atual e outros podem ser mais complexos, onde você precisa filtrar em um intervalo específico de datas exclusivas. Saiba mais em medidas em Power pivot e funções de inteligência de tempo.

Apêndice

Converter tipos 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 texto. Ou seja, uma data que aparece como 2012-12-04T11:47:09 não é uma data, ou pelo menos não o tipo de data pivot que o Power pivot pode entender. É apenas texto que lê como 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 data, ambas as colunas devem ser do tipo de dados de Data .

Geralmente, quando você tenta alterar o tipo de dados de uma coluna de datas que são tipos de dados de texto para um tipo de dados de data, o Power pivot pode interpretar as datas e convertê-las em um tipo de dados de data real automaticamente. Se o Power pivot não puder fazer uma conversão de tipo de dados, você receberá um erro de tipo incompatível.

No entanto, você ainda pode converter as datas em um tipo de dados de data real. 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, concatenar novamente de forma que o Power pivot possa ser lido como uma data real.

Neste exemplo, importamos uma tabela de fatos chamada Sales no Power pivot. Ele contém uma coluna chamada DateTime. Os valores aparecem assim:

Colunas DateTime em uma tabela de fatos.

Se olharmos o tipo de dados na guia página inicial do Power pivot do grupo, vemos que é o tipo de dados texto.

Tipo de dados na faixa de opções

Não é possível criar uma relação entre a coluna DateTime e a coluna data na tabela de data porque os tipos de dados não correspondem. Se tentarmos alterar o tipo de dados para Data, obtemos um erro de tipo incompatível:

Erro de incompatibilidade

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 real, precisamos criar uma nova coluna que analise o texto e recriá-lo em um valor que o Power pivot possa criar um tipo de dados de data.

Lembre-se da seção trabalhando com tempo apresentada anteriormente neste artigo; a menos que seja necessário que sua análise seja para um nível de tempo do dia de precisão, você deve converter as datas na tabela de fatos em um nível de precisão de dia. Com isso em mente, queremos que os valores na nossa nova coluna tenham o nível de precisão do dia (excluindo o tempo). Podemos converter os valores na coluna DateTime para um tipo de dados de data e remover o nível de tempo de precisão com a fórmula a seguir:

= Data (esquerda ([DateTime], 4), ext. texto ([dataHora], 6, 2), ext. texto ([DateTime], 9, 2))

Isso nos dá uma nova coluna (neste caso, data nomeada). O Power pivot ainda detecta os valores a serem datas e define o tipo de dados automaticamente para data.

Coluna de data na tabela de fatos

Se quisermos preservar o nível de tempo da precisão, basta estender a fórmula para incluir as horas, minutos e segundos.

= Data (esquerda ([DateTime], 4), ext. texto ([DateTime], 6, 2), ext. texto ([DateTime], 9, 2)) +

TEMPO (médio ([DateTime], 12, 2), MID ([DateTime], 15, 2), MID ([DateTime], 18, 2))

Agora que temos uma coluna de data do tipo de dados de data, podemos criar uma relação entre ela e uma coluna de data em uma data.

Recursos adicionais

Datas no Power Pivot

Cálculos no Power Pivot

Início rápido: aprenda os fundamentos de DAX em 30 minutos

Referência de expressões de análise de dados

Centro de recursos Dax

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×