Entender e criar tabelas de data no Power Pivot no Excel

Criar uma tabela de datas em Excel

Você pode criar uma tabela de datas no Excel e, em seguida, copiá-la em uma nova tabela no Modelo de Dados. Isso é realmente muito fácil de fazer e oferece muita flexibilidade.

Quando você cria uma tabela de datas Excel, você começa com uma única coluna com um intervalo contíguo de datas. Em seguida, você pode criar colunas adicionais, como Year, Quarter, Month, Fiscal Year, Period, etc. na planilha Excel usando fórmulas Excel ou, depois de copiar a tabela para o Modelo de Dados, você pode cria-las como colunas calculadas. A criação de colunas de data adicionais no Power Pivot é descrita na seção Adicionando Novas Colunas de Data à Tabela de Datas posteriormente neste artigo.

Adicionando 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 em um intervalo de datas. Também é necessário para criar uma relação entre a tabela de fatos e a tabela de datas. Mas essa coluna de data única com uma linha para cada dia não é útil ao analisar por datas em uma tabela dinâmica ou Power View relatório. Você deseja que sua tabela de datas inclua colunas que ajudam você a agregar seus dados para um intervalo ou grupo de datas. Por exemplo, você pode querer somar valores de 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 datas precisa de colunas ano, mês ou trimestre que permitem agregar seus dados para esse período.

Se você importou sua tabela de datas de uma fonte de dados relacional, ela já pode incluir os diferentes tipos de colunas de data que você deseja. 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 datas no Excel e copiá-la para o Modelo de Dados. Felizmente, a criação de novas colunas de datas no Power Pivot é bastante fácil com funções de data e hora no DAX.

Funções data e hora do DAX

Se você já trabalhou com funções de data e hora em Excel fórmulas, provavelmente estará familiarizado com as funções Data e Hora. Embora essas funções sejam semelhantes às suas contrapartes no Excel, há algumas diferenças importantes:

• As funções Data e Hora do DAX usam um tipo de dados de data/hora.

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

• Eles podem ser usados para retornar e/ou manipular valores de data.

Essas funções são frequentemente usadas ao criar colunas de data personalizadas em uma tabela de datas, portanto, elas são importantes para entender. Vamos usar várias dessas funções para criar colunas para Year, Quarter, FiscalMonth e assim por diante.

O DAX inclui as seguintes funções Data e Hora:

• DATE

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• HOUR

• MINUTE

• MONTH

• AGORA

• SECOND

• TIME

• TIMEVALUE

• HOJE

• WEEKDAY

• WEEKNUM

• YEAR

• YEARFRAC

Há muitas outras funções DAX que você também pode usar em suas fórmulas. Por exemplo, muitas das fórmulas descritas aqui usam Funções Matemáticas e Trigonométricas como MOD e TRUNC,Funções Lógicas como IFe Funções de Texto como FORMAT Para obter mais informações sobre outras funções DAX, consulte a seção Recursos Adicionais posteriormente neste artigo.

Exemplos de fórmula para um ano de calendário

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

Nome do mês

=FORMAT([date],"mmmm")

Nesta fórmula, para obter o nome do mês, usamos 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 nosso nome de mês mostre todos os caracteres, portanto, usamos "mmmm". Nosso resultado tem esta aparência:

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

Dia da semana

=FORMAT([date],"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 format.

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 Vendas em VALUES e Ano e Trimestre da tabela de dimensão Calendário em ROWS. SalesAmount é agregado para contexto de ano e trimestre.

Exemplos de fórmula para um ano fiscal

Feriados ou datas especiais

Talvez você queira incluir uma coluna de datas 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 slicer ou filtro. Em outros casos, talvez você queira excluir essas datas de outras colunas de data ou em uma medida.

Incluir feriados ou dias especiais é bastante simples. Você pode criar uma tabela em Excel que tenha as datas que deseja incluir. Em seguida, você pode copiar ou usar Add to Data Model para adicioná-lo ao Modelo de Dados como uma tabela vinculada. Na maioria dos casos, não é necessário criar uma relação entre a tabela e a tabela Calendar. Qualquer fórmula que faz referência a ela pode usar a função LOOKUPVALUE para retornar valores.

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

Na tabela de datas, criamos uma coluna chamada Holiday e usamos uma fórmula como esta:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Vamos ver essa fórmula com mais cuidado.

Usamos a função LOOKUPVALUE para obter valores da coluna Feriado na tabela Feriados. No primeiro argumento, especificamos a coluna onde nosso valor de resultado estará. Especificamos a coluna Feriado na tabela Feriados porque esse é o valor que desejamos retornar.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

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(Holidays[Holiday],Holidays[date],Calendar[date])

Por fim, especificamos a coluna em nossa tabela Calendário que tem as datas que desejamos pesquisar na tabela Feriado. Isso, claro, é a coluna Data na tabela Calendário.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

A coluna Feriado retornará o nome de feriado para cada linha que tenha um valor de data que corresponde a uma data na tabela Feriados.

Calendário personalizado - treze períodos de quatro semanas

Algumas organizações, como o serviço de varejo ou de alimentação, geralmente relatam períodos diferentes, como treze períodos de quatro semanas. Com um calendário de 13 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 se enquadram no mesmo período a cada ano. Você pode optar por iniciar um período em qualquer dia da semana. Assim como 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.

Período

O período neste calendário personalizado contém 28 dias e sempre começará em um domingo. Esta coluna retornará o número do período que começa com o primeiro domingo do primeiro ano fiscal.

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

Vamos ver essa fórmula com mais cuidado.

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

=INT([Week])

Em seguida, adicione 3 a esse valor, assim:

=INT([Week]+3)

Em seguida, divida o resultado por 4, assim:

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

O resultado tem esta aparência:

Ano Fiscal do Período

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

=INT(([Period]+12)/13)+2008

Vamos ver 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, pois há 13 períodos de 28 dias no ano fiscal:

=(([Period]+12)/13)

Adicionamos 2010, pois 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 tem esta aparência:

Período em FiscalYear

Esse valor retorna o número de período, de 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, portanto, vamos descrevê-la primeiro em um idioma que entendemos melhor. Esta 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 as funções MOD (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 da tabela de datas do calendário de exemplo. Podemos tratar disso com uma função IF. Caso nosso resultado seja 0, retornaremos 13, assim:

=IF(MOD([Period],13),MOD([Period],13),13)

O resultado tem esta aparência:

Vários relacionamentos

Em alguns casos, pode ser necessário criar várias relações ou criar várias tabelas de datas. Por exemplo, se houver vários campos de data na tabela de fatos Vendas, como DateKey, ShipDate e ReturnDate, todos eles poderão ter relações com o campo Data na tabela Data do calendário, 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 seria melhor servir 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 Total de Vendas, com a fórmula Total de Vendas:=SUM([SalesAmount]), é colocada em VALUES, e os campos FiscalYear e FiscalQuarter da tabela de datas do calendário são colocados em ROWS.

Essa Tabela Dinâmica direta funciona corretamente porque queremos somar o total de vendas pela data datransação em DateKey. Nossa medida Total de Vendas usa as datas em 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 Data.

Relações inativas

Mas, e se nós quisermos somar nosso total de vendas não por data de transação, mas por data de lançamento? 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çãos serão sempre baseadas na data da transação. No entanto, podemos ter várias relações, mesmo que 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, ShipDate tem uma relação inativa, portanto, qualquer fórmula de medida criada para agregar dados com base em datas de nave 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 lançamento. Usamos uma fórmula como esta para especificar a relação a ser usada:

Total de vendas por data de nave:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Esta fórmula simplesmente declara: 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 Nave em VALORES, e Ano Fiscal e Trimestre Fiscal em LINHAS, veremos o mesmo Total Geral, mas todos os outros valores de soma para o ano fiscal e o trimestre fiscal são diferentes porque eles são baseados na data do barco 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 Nave), referenciam a relação inativa em sua fórmula. Há outra alternativa, ou seja, usar várias tabelas de datas.

Várias tabelas de data

Outra maneira de trabalhar com várias colunas de datas em sua tabela de fatos é criar várias tabelas de datas e criar relações ativas separadas entre elas. Vamos ver nosso exemplo de tabela de vendas novamente. Temos três colunas com datas em que podemos querer agregar dados:

• Um DateKey com a data de venda para cada transação.

• 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 é o mais importante. Faremos a maioria de nossas agregaçãos 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 fórmulas de medida especial, podemos criar tabelas de datas adicionais para data 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 datas estão em uma tabela de datas diferente, queremos nomeá-las de forma 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 ao criarmos uma relação inativa e um campo calculado Especial Total de Vendas por Data de Nave.

Cada uma dessas abordagens requer uma consideração cuidadosa. Ao usar várias relações com uma única tabela de datas, talvez seja preciso criar medidas especiais que transitem em 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, ela exigirá mais memória. Experimente o que funciona melhor para você.

Como: definir a propriedade Date Table

1. Na janela PowerPivot, selecione a tabela Calendário.

2. Na guia Design, clique em Marcar como tabela de datas.

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

Converter datas de tipo de dados de texto para um tipo de dados de data

Em alguns casos, uma tabela de fatos com dados de transação pode conter datas do tipo de dados de texto. Ou seja, uma data que aparece como 2012-12-04T11:47:09 não é de fato uma data, ou pelo menos não o tipo de data que o Power Pivot pode entender. Na verdade, é apenas texto que se lê como uma data. Para criar uma relação entre uma coluna de datas na tabela de fatos e uma coluna de data em uma tabela de datas, ambas as colunas devem ser do tipo de dados Date.

Normalmente, quando você 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 em um tipo de dados de data verdadeiro automaticamente. Se o Power Pivot não puder fazer uma conversão de tipo de dados, você receberá um erro de incompatibilidade de tipo.

No entanto, você ainda pode converter as datas em um tipo de dados de data verdadeiro. 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 concatená-la novamente de uma maneira que o Power Pivot possa ler como uma data verdadeira.

Neste exemplo, importamos uma tabela de fatos chamada Vendas para o Power Pivot. Ele contém uma coluna chamada DateTime. Os valores aparecem assim:

Se olharmos para o Tipo de Dados na guia Página Início do grupo de formatação do Power Pivot, veremos que é tipo de dados text.

Não é possível criar uma relação entre a coluna DateTime e a coluna Data em nossa tabela de datas porque os tipos de dados não combinam. Se tentarmos alterar o tipo de dados para Date,obteremos um erro de incompatibilidade de tipo:

Nesse caso, o Power Pivot não pôde converter o tipo de dados do texto para a data. Ainda podemos usar essa coluna, mas, para que ela seja usada em um tipo de dados de data verdadeiro, precisamos criar uma nova coluna que analisará o texto e a crie em um valor que o Power Pivot possa tornar um tipo de dados Date.

Lembre-se, na seção Trabalhando com tempo anteriormente neste artigo; a menos que seja necessário que sua análise seja para um nível de precisão de hora do dia, você deve converter datas em sua tabela de fatos para um nível de precisão de dia. Com isso em mente, queremos que os valores em nossa nova coluna sejam no 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 precisão de hora com a seguinte fórmula:

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

Isso nos dá uma nova coluna (nesse caso, chamada Date). O Power Pivot até detecta os valores como datas e define o tipo de dados automaticamente como Data.

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

=DATE(LEFT([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 Date, 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

Central de Recursos do DAX