Entender e criar tabelas de data no Power Pivot no Excel

Create uma tabela de datas no Excel

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

Ao criar uma tabela de datas no Excel, você começa com uma única coluna com um intervalo contíguo de datas. Em seguida, você pode criar colunas adicionais como Ano, Trimestre, Mês, Ano Fiscal, Período etc. na planilha do Excel usando fórmulas do Excel ou, depois de copiar a tabela no 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 de data à seção 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 tem 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 um relatório da Tabela Dinâmica ou do Power View. Você deseja que sua tabela de datas inclua colunas que ajudam 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 calcula 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 data de uma fonte de dados relacional, ela já poderá incluir 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 datas no Excel e copiá-la no Modelo de Dados. Felizmente, criar novas colunas de data no Power Pivot é muito fácil com funções de data e hora no DAX.

Funções de data e hora do DAX

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

• As funções DAX Date e Time usam um tipo de dados datetime.

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

• Eles podem ser usados 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 datas, portanto, elas são importantes para entender. Usaremos várias dessas funções para criar colunas para Ano, Trimestre, FiscalMonth e assim por diante.

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

• DATA

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• HORA

• MINUTO

• MÊS

• NWO

• SEGUNDO

• TEMPO

• TIMEVALUE

• HOJE

• SEMANA

• WEEKNUM

• ANO

• YEARFRAC

Há muitas outras funções DAX que você pode usar em suas fórmulas também. 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 IF e Funções de Texto como FORMAT Para obter mais informações sobre outras funções DAX, consulte a seção Recursos Adicionais mais adiante neste artigo.

Exemplos de fórmula para um ano civil

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.

Mês Nome

=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 Date 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 se parece com este:

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

Dia da semana

=FORMAT([date],"ddd")

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

Exemplo de Tabela Dinâmica

Depois de ter campos para datas como Ano, Trimestre, Mês etc., você pode usá-los em uma Tabela Dinâmica ou relatório. Por exemplo, a imagem a seguir mostra o campo SalesAmount da tabela De fatos de vendas em VALUES e Ano e Trimestre da tabela de dimensões calendário em ROWS. SalesAmount é agregado para o contexto ano e trimestre.

Exemplos de fórmula para um ano fiscal

Feriados ou datas especiais

Você pode querer incluir uma coluna de data que indica que determinadas datas são feriados ou alguma outra data especial. Por exemplo, você pode querer somar totais de vendas para o Dia de Ano Novo adicionando um campo Holiday a uma Tabela Dinâmica, como uma segmentação ou filtro. Em outros casos, você pode querer excluir essas datas de outras colunas de data ou em uma medida.

Incluir feriados ou dias especiais é bastante simples. Você pode criar uma tabela no Excel que tenha as datas que deseja incluir. Em seguida, você pode copiar ou usar Adicionar ao Modelo de Dados para adicioná-lo ao Modelo de Dados como uma tabela vinculada. Na maioria dos casos, não é necessário criar uma relação entre a tabela e a tabela Calendário. Todas as fórmulas que fazem referência a ela podem usar a função LOOKUPVALUE para retornar valores.

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

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

=LOOKUPVALUE(Feriados[Feriado],Feriados[data],Calendário[data])

Vamos examinar essa fórmula com mais cuidado.

Usamos a função LOOKUPVALUE para obter valores da coluna Holiday na tabela Feriados. No primeiro argumento, especificamos a coluna em que nosso valor de resultado será. Especificamos a coluna Holiday na tabela Feriados porque esse é o valor que queremos que seja retornado.

=LOOKUPVALUE(Feriados[Feriado],Feriados[data],Calendário[data])

Em seguida, especificamos o segundo argumento, a coluna de pesquisa que tem as datas que desejamos pesquisar. Especificamos a coluna Data na tabela Feriados , assim:

=LOOKUPVALUE(Feriados[Feriado],Feriados[data],Calendário[data])

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

=LOOKUPVALUE(Feriados[Feriado],Feriados[data],Calendário[data])

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

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

Algumas organizações, como varejo ou serviço de alimentação, geralmente relatam períodos diferentes, como treze períodos de quatro semanas. Com um calendário de treze quatro semanas, cada período é de 28 dias; Portanto, cada período contém quatro segundas- feiras, quatro terças-feiras, quatro quartas-feiras e assim por diante. Cada período contém o mesmo número de dias e, normalmente, os feriados cairão no mesmo período a cada ano. Você pode optar por iniciar um período em qualquer dia da semana. Assim como acontece com datas em um calendário ou ano fiscal, você pode usar o DAX para criar colunas adicionais com datas personalizadas.

Nos exemplos abaixo, o primeiro período completo começa no primeiro domingo do ano fiscal. Nesse caso, o ano fiscal começa em 7/1.

Período

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

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

Vamos examinar essa fórmula com mais cuidado.

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

=INT([Semana])

Em seguida, adicione 3 a esse valor, assim:

=INT([Semana]+3)

Em seguida, divida o resultado por 4, assim:

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

O resultado se parece com este:

Ano Fiscal do Período

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

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

Vamos examinar essa fórmula com mais cuidado.

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

= ([Period]+12)

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

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

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

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

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

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

O resultado se parece com este:

Período no Ano Fiscal

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

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

Essa fórmula é um pouco mais complexa, por isso vamos descrevê-la primeiro em uma linguagem que entendemos melhor. Essa fórmula afirma, divida o valor de [Período] por 13 para obter um número de período (1-13) no ano. Se esse número for 0, retorne 13.

Primeiro, criamos uma fórmula que retorna o restante do valor de Period por 13. Podemos usar o MOD (funções Matemática e Trigonométrica) assim:

=MOD([Period],13)

Isso, na maioria das vezes, nos dá o resultado que queremos, exceto onde o valor para Period é 0 porque essas datas não se enquadram no primeiro ano fiscal, como nos primeiros cinco dias da nossa tabela de data de calendário de exemplo. Podemos cuidar disso com uma função IF. Caso nosso resultado seja 0, retornaremos 13, assim:

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

O resultado se parece com este:

Várias relações

Em alguns casos, pode ser necessário criar várias relações ou criar várias tabelas de datas. Por exemplo, se houver vários campos de data na tabela De fatos de Vendas, como DateKey, ShipDate e ReturnDate, todos eles poderão ter relações com o campo Date na tabela Data, mas apenas um deles pode ser uma relação ativa. Nesse caso, como DateKey representa a data da transação e, portanto, a data mais importante, isso serviria melhor como a relação ativa . Os outros têm relações inativas.

A Tabela Dinâmica a seguir calcula o total de vendas por Ano Fiscal e Trimestre Fiscal. Uma medida denominada Vendas Totais, com a fórmula Total Sales:=SUM([SalesAmount]), é colocada em VALORES, e os campos FiscalYear e FiscalQuarter da tabela de datas calendário são colocados em LINHAS.

Essa Tabela Dinâmica direta funciona corretamente porque queremos somar nossas vendas totais até a data da transação no DateKey. Nossa medida Vendas Totais usa as datas no DateKey e é resumida por ano fiscal e trimestre fiscal porque há uma relação entre DateKey na tabela Vendas e a coluna Data na tabela de datas calendário.

Relações inativas

Mas, e se quiséssemos resumir nossas vendas totais não por data de transação, mas por data de envio? Precisamos de uma relação entre a coluna ShipDate na tabela Vendas e a coluna Data na tabela Calendário. Se não criarmos essa relação, nossas agregações serão sempre baseadas na data da transação. No entanto, podemos ter várias relações, embora apenas uma possa estar ativa e, como a data da transação é a mais importante, ela obtém a relação ativa com a tabela Calendário.

Nesse caso, o ShipDate tem uma relação inativa, portanto, qualquer fórmula de medida criada para agregar dados com base em datas de navio deve especificar a relação inativa usando a função USERELATIONSHIP .

Por exemplo, como há uma relação inativa entre a coluna ShipDate na tabela Vendas e a coluna Data na tabela Calendário, podemos criar uma medida que soma o total de vendas por data de envio. Usamos uma fórmula como esta para especificar a relação a ser usada:

Vendas totais por data do navio:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Essa fórmula simplesmente afirma: calcular uma soma para SalesAmount, mas filtrar usando a relação entre a coluna ShipDate na tabela Vendas e a coluna Data na tabela Calendário.

Agora, se criarmos uma Tabela Dinâmica e colocarmos a medida Total de Vendas por Data de Navio em VALORES e Ano Fiscal e Trimestre Fiscal em LINHAS, veremos o mesmo Grande Total, mas todos os outros valores de soma para o ano fiscal e o trimestre fiscal são diferentes porque são baseados na data do navio e não na data da transação.

O uso de relações inativas permite que você use apenas uma tabela de datas, mas exige que todas as medidas (como Total de Vendas por Data de Navio), referenciem a relação inativa em sua fórmula. Há outra alternativa, ou seja, usar várias tabelas de datas.

Várias tabelas de datas

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

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

• Um ShipDate – com a data e a hora em que os itens vendidos foram enviados para o cliente.

• Um ReturnDate – com a data e a hora em que um ou mais itens retornados foram recebidos.

Lembre-se de que o campo DateKey com a data da transação é mais importante. Faremos a maioria de nossas agregações com base nessas datas, portanto, certamente queremos uma relação entre ela e a coluna Data na tabela Calendário. Se não quisermos criar relações inativas entre ShipDate e ReturnDate e o campo Data na tabela Calendário, exigindo assim fórmulas de medidas especiais, podemos criar tabelas de datas adicionais para data de envio e data de retorno. Em seguida, podemos criar relações ativas entre eles.

Neste exemplo, criamos outra tabela de datas chamada ShipCalendar. Isso, é claro, também significa criar colunas de datas adicionais e, como essas colunas de data estão em uma tabela de datas diferente, queremos nomeá-las de maneira que as diferencie das mesmas colunas na tabela Calendário. Por exemplo, criamos colunas chamadas ShipYear, ShipMonth, ShipQuarter e assim por diante.

Se criarmos nossa Tabela Dinâmica e colocarmos nossa medida de Vendas Totais em VALUES e ShipFiscalYear e ShipFiscalQuarter em LINHAS, veremos os mesmos resultados que vimos quando criamos uma relação inativa e um campo calculado total de Vendas por Data de Navio especial.

Cada uma dessas abordagens requer uma consideração cuidadosa. Ao usar várias relações com uma única tabela de datas, talvez seja necessário criar medidas especiais que transitem em relações inativas usando a função USERELATIONSHIP. Por outro lado, criar várias tabelas de datas pode ser confuso em uma Lista de Campos e, como você tem mais tabelas no Modelo de Dados, isso exigirá mais memória. Experimente o que funciona melhor para você.

Como definir a propriedade Tabela de Data

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 Date.

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

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

Normalmente, quando você tenta alterar o tipo de dados para 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 real 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 verdadeira. Você pode criar uma nova coluna calculada e usar uma fórmula DAX para analisar o ano, mês, dia, hora etc. das cadeias de caracteres de texto e, em seguida, concatená-la novamente de uma maneira que o Power Pivot possa ler como uma data verdadeira.

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

Se examinarmos o Tipo de Dados na guia Página Inicial do Grupo de Formatação do Power Pivot, veremos que ele é tipo de dados de texto.

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

Nesse caso, o Power Pivot não pôde converter o tipo de dados de texto em data. Ainda podemos usar esta coluna, mas para obtê-la em um tipo de dados de data verdadeira, precisamos criar uma nova coluna que analise o texto e o recria em um valor que o Power Pivot possa criar um tipo de dados Date.

Lembre-se, da seção Trabalhar com tempo anterior 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 em um nível diário de precisão. Com isso em mente, queremos que os valores em nossa nova coluna estejam no nível diurno de precisão (excluindo a hora). Podemos converter os valores na coluna DateTime em um tipo de dados de data e remover o nível de tempo de precisão 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 Date.

Se quisermos preservar o nível de tempo de precisão, 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 Date 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

Centro de Recursos DAX