Compreender e criar tabelas de data no Power Pivot no Excel

Criar uma tabela de datas no Excel

Pode criar uma tabela de datas no Excel em seguida, copiá-la para uma nova tabela no Modelo de Dados. Isto é muito fácil de fazer e dá-lhe muita flexibilidade.

Quando cria uma tabela de datas no Excel, começa com uma única coluna com um intervalo contíguo de datas. Em seguida, pode criar colunas adicionais, como Ano, Trimestre, Mês, Ano Fiscal, Período, etc., na livro Excel utilizando fórmulas Excel ou, depois de copiar Excel tabela para o Modelo de Dados, pode criá-las como colunas calculadas. A criação de colunas de data adicionais no Power Pivot está descrita na secção Adicionar Novas Colunas de Data à Tabela de Datas mais à frente neste artigo.

Adicionar novas colunas de data à tabela de datas

Uma tabela de datas com uma coluna de data única que tenha uma linha para cada dia para 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. Contudo, essa coluna de data única com uma linha para cada dia não é útil ao analisar por datas numa tabela dinâmica ou Power View relatório. Pretende que a sua tabela de datas inclua colunas que o ajudem a agregar os seus dados de um intervalo ou grupo de datas. Por exemplo, poderá querer somar os montantes de vendas por mês ou trimestre ou pode criar uma medida que calcula o crescimento anual ao longo do ano. Em cada um destes casos, a sua tabela de datas necessita de colunas de ano, mês ou trimestre que lhe permitam agregar os seus dados para esse período.

Se tiver importado a sua tabela de datas a partir de uma origem de dados relacional, esta poderá já incluir os diferentes tipos de colunas de data que pretende. Em alguns casos, poderá querer modificar algumas dessas colunas ou criar colunas de data adicionais. Isto é especialmente verdadeiro se criar a sua própria tabela de datas no Excel e copiá-la para o Modelo de Dados. Felizmente, criar novas colunas de data no Power Pivot é bastante fácil com as Funções de Data e Hora no DAX.

Funções de Data e Hora do DAX

Se alguma vez trabalhou com funções de data e hora em fórmulas Excel, é provável que esteja familiarizado com as Funções de Data e Hora. Embora estas funções sejam semelhantes às suas homólogos Excel, existem algumas diferenças importantes:

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

• Podem assumir 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 são importantes de compreender. Iremos utilizar várias destas funções para criar colunas para Ano, Trimestre, Fim Fiscal, entre outras.

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

• DATA

• DATA.VALOR

• NEXTDAY

• EDATE

• EOMONTH

• HORA

• MINUTO

• MÊS

• AGORA

• SECOND

• TIME

• VALOR.TEMPO

• HOJE

• DIA.SEMANA

• WEEKNUM

• ANO

• FRAÇÃO ANO

Também existem muitas outras funções DAX que pode utilizar nas suas fórmulas. Por exemplo, muitas das fórmulas descritas aqui utilizam Funções Matemáticas e Trigonométricas como RESTO e TRUNQUE,Funções Lógicas como SE e Funções de Texto como FORMAT Para obter mais informações sobre outras funções DAX, consulte a secção Recursos Adicionais mais à frente neste artigo.

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

Os seguintes exemplos descrevem fórmulas utilizadas para criar colunas adicionais numa tabela de datas denominada Calendário. Uma coluna, denominada Data, já existe e contém um intervalo contíguo de datas de 1/1/2010 a 31/12/2016.

Nome do Mês

=FORMAT([data],"mmmm")

Nesta fórmula, para obter o nome do mês, utilizamos a função FORMATAR 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 mês mostre todos os carateres, pelo que utilizamos "mmmm". O nosso resultado tem o mesmo aseque isto:

Se quisermos devolver o nome do mês abreviado para três letras, utilizaríamos "mmm" no argumento formato.

Dia da Semana

=FORMAT([data],"ddd")

Nesta fórmula, utilizamos a função FORMAT para obter o nome do dia. Uma vez que queremos apenas um nome abreviado para o dia, especificamos "ddd" no argumento formato.

Tabela Dinâmica de Exemplo

Assim que tiver campos para datas como Ano, Trimestre, Mês, etc., pode usá-los numa Tabela Dinâmica ou relatório. Por exemplo, a imagem seguinte mostra o campo SalesAmount da tabela de factos Vendas em VALORES e Ano e Trimestre da tabela de dimensão Calendário em LINHAS. SalesAmount é agregado para o contexto anual e trimestral.

Exemplos de fórmulas para um ano fiscal

Feriados ou datas especiais

Poderá querer incluir uma coluna de data que indique que determinadas datas são feriados ou outra data especial. Por exemplo, poderá querer somar os totais de vendas do Dia de Ano Novo ao adicionar um campo Feriado a uma Tabela Dinâmica, como uma filtragem ou a uma filtragem. Noutros casos poderá querer excluir essas datas de outras colunas de datas ou de uma medida.

Incluir feriados ou dias especiais é bastante simples. Pode criar uma tabela no Excel com as datas que pretende incluir. Em seguida, pode copiar ou utilizar Adicionar a Modelo de Dados para adicioná-lo 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 Calendário. Todas as fórmulas que a referenciem podem utilizar a função VALOR.UP para devolver valores.

Segue-se um exemplo de uma tabela criada no Excel que inclui feriados a adicionar à tabela de datas:

Na tabela de datas, criamos uma coluna denominada Feriados e utilizamos uma fórmula como esta:

=VALOR.UP(Feriados[Feriado],Feriados[data],Calendário[data])

Vamos olhar para esta fórmula com mais atenção.

Utilizamos a função VALOR.UP para obter valores da coluna Feriados na tabela Feriados. No primeiro argumento, especificamos a coluna onde o nosso valor de resultado será. Especificamos a coluna Feriados na tabela Feriados porque é esse o valor que pretendemos devolvido.

=VALOR.UP(Feriados[Feriado],Feriados[data],Calendário[data])

Em seguida, especificamos o segundo argumento, a coluna de pesquisa que tem as datas que pretendemos procurar. Especificamos a coluna Data na tabela Feriados, da seguinte forma:

=VALOR.UP(Feriados[Feriado],Feriados[data],Calendário[data])

Por fim, especificamos a coluna na tabela do Calendário que tem as datas que pretendemos procurar na tabela Feriados. Esta é, claro, a coluna Data na tabela Calendário.

=VALOR.UP(Feriados[Feriado],Feriados[data],Calendário[data])

A coluna Feriados irá devolver 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 o retalho ou o serviço de alimentação, comunicam frequentemente sobre períodos diferentes, como treze períodos de quatro semanas. Com um calendário de treze dias de quatro semanas, cada período é de 28 dias; assim, cada período contém quatro segundas-feiras, quatro terças-feiras, quatro quartas-feiras, entre outros. Cada período contém o mesmo número de dias e, normalmente, os feriados recaem no mesmo período de cada ano. Pode optar por iniciar um período em qualquer dia da semana. Tal como 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.

Period

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

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

Vamos olhar para esta fórmula com mais atenção.

Primeiro, criamos uma fórmula que devolve um valor da coluna Semana como um valor inteiro, da seguinte forma:

=INT([Semana])

Em seguida, adicione 3 a esse valor, desta forma:

=INT([Semana]+3)

Em seguida, divida o resultado por 4, desta forma:

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

O resultado tem o isto:

Período Ano Fiscal

Este valor devolve o ano fiscal de um período.

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

Vamos olhar para esta fórmula com mais atenção.

Primeiro, criamos uma fórmula que devolve um valor do Período e adiciona 12:

= ([Período]+12)

O resultado é dividido por 13, uma vez que existem treze períodos de 28 dias no ano fiscal:

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

Adicionamos 2010 porque este é o primeiro ano na tabela:

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

Por fim, utilizamos a função INT para remover qualquer fração do resultado e devolvemos um número inteiro quando dividido por 13, da mesma forma:

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

O resultado tem o isto:

Período no Ano Fiscal

Este valor devolve o número de período, 1 a 13, começando pelo primeiro Período completo (a começar ao domingo) em cada ano fiscal.

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

Esta fórmula é um pouco mais complexa, pelo que vamos descrevê-la primeiro num idioma que melhor compreendemos. Esta fórmula indica que divida o valor de [Período] por 13 para obter o número de período (1-13) no ano. Se esse número for 0, então devolve 13.

Primeiro, criamos uma fórmula que devolve o resto do valor de Período por 13. Podemos utilizar a função MOD (funções matemáticas e trigonométricas) da mesma forma:

=MOD([Período],13)

Isto, na maioria dos casos, dá-nos o resultado que pretendemos, exceto em que o valor do Período é 0 porque essas datas não se insirem no primeiro ano fiscal, como nos primeiros cinco dias da nossa tabela de datas do Calendário de exemplo. Podemos resolver este problema com uma função SE. Se o nosso resultado for 0, devolvemos 13, desta forma:

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

O resultado tem o isto:

Relações múltiplas

Em alguns casos, poderá ser necessário criar múltiplas relações ou criar múltiplas tabelas de datas. Por exemplo, se existem múltiplos campos de data na tabela de factos Vendas, como DataDeData, DataDeDeVinha e DataDeVolvolção, todos podem ter relações com o campo Data na tabela de data do Calendário, mas apenas uma dessas pode ser uma relação ativa. Neste caso, uma vez que DateKey representa a data da transação e, por conseguinte, a data mais importante, isto serve melhor como a 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([SalesAmount]), é colocada em VALORES e os campos Ano Fiscal eQuarde Fiscal da tabela de data Calendário são colocados em LINHAS.

Esta tabela dinâmica direta funciona corretamente porque queremos somar as nossas vendas totais até à data datransação na DateKey. A nossa medida Total de Vendas utiliza as datas em DateKey e é somada por ano fiscal e trimestre fiscal porque existe uma relação entre DateKey na tabela Vendas e a coluna Data na tabela Data do Calendário.

Relações inativas

Mas e se quiséssemos somar as nossas vendas totais não por data de transação, mas por data de lançamento? Precisamos de uma relação entre a coluna DataDeEdimento na tabela Vendas e a coluna Data na tabela Calendário. Se não criarmos essa relação, as nossas agregações são sempre baseadas na data da transação. No entanto, podemos ter várias relações, embora apenas uma possa estar ativa e porque a data de transação é a mais importante, obtemos a relação ativa com a tabela Calendário.

Neste caso, DataDeCesso tem uma relação inativa, pelo que qualquer fórmula de medida criada para agregar dados com base em datas de expedição tem de especificar a relação inativa utilizando a função USERELATIONSHIP.

Por exemplo, uma vez que existe uma relação inativa entre a coluna DataDePaís 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. Utilizamos uma fórmula como esta para especificar a relação a utilizar:

Total de Vendas por Data de Expedição:=CALCULATE(SOMA(Vendas[MontanteDas Vendas]), USERELATIONSHIP(Vendas[DataDeTransceção], Calendário[Data]))

Esta fórmula só indica: Calcular uma soma para SalesAmount, mas filtrar utilizando a relação entre a coluna DataDeTransceção na tabela Vendas e a coluna Data na tabela Calendário.

Agora, se criarmos uma tabela dinâmica e colocarmos o total de Vendas por Data de Lançamento em VALORES e Ano Fiscal e Trimestre Fiscal em LINHAS, vemos o mesmo Total Geral, mas todos os outros montantes de somas do ano fiscal e do trimestre fiscal são diferentes porque são baseados na data de expedição e não na data de transação.

A utilização de relações inativas permite-lhe utilizar apenas uma tabela de datas, mas exige que as medidas (como Total de Vendas por Data de Lançamento) referenciam a relação inativa na sua fórmula. Existe outra alternativa, ou seja, utilizar várias tabelas de datas.

Múltiplas tabelas de datas

Outra forma de trabalhar com múltiplas colunas de data na sua tabela de factos é criar múltiplas tabelas de datas e criar relações ativas separadas entre as mesmas. Voltemos ao exemplo da tabela Vendas. Temos três colunas com datas em que podemos agregar dados:

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

• Uma DataDePaís, com a data e hora em que os itens vendidos foram enviados para o cliente.

• Uma DataDeVolvida – com a data e hora em que um ou mais item devolvido foi recebido.

Lembre-se de que o campo DateKey com a data de transação é mais importante. Iremos fazer a maior parte das nossas agregações com base nestas datas, pelo que iremos querer, com certeza, uma relação entre a mesma e a coluna Data na tabela Calendário. Se não quisermos criar relações inativas entre DataDeVagem e DataDeVolvido e o campo Data na tabela Calendário, assim que requerem fórmulas de medida especiais, podemos criar tabelas de datas adicionais para a data de entrega e a data de devolução. Podemos então criar relações ativas entre elas.

Neste exemplo, criámos outra tabela de datas denominada ShipCalendar. Isto também significa criar colunas de data adicionais e, uma vez que estas colunas de datas estão numa tabela de datas diferente, queremos dar-lhes nomes que as diferenciam das mesmas colunas na tabela Calendário. Por exemplo, criámos colunas com o nome AnoDeMeu,Month, ShipQuarter, entre outros.

Se criarmos a nossa Tabela Dinâmica e colocarmos a nossa medida Total de Vendas em VALORES e ShipFiscalYear e ShipFiscalQuarter em LINHAS, vemos os mesmos resultados que vimos quando criamos uma relação inativa e um campo calculado Total de Vendas Por Data de Enviado especial.

Cada uma destas abordagens exige uma atenção cuidada. Ao utilizar múltiplas relações com uma única tabela de data, poderá ter de criar medidas especiais que transitem relações inativas utilizando a função USERELATIONSHIP. 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, irá precisar de mais memória. Experimente aquilo que funciona melhor para si.

Como: Definir a propriedade Tabela de Datas

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

2. No separador Estrutura, clique em Marcar como Tabela de Datas.

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

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ções pode conter datas de tipo de dados de texto. Ou seja, uma data que aparece como 12-12-04T11:47:09 não é, de todo, uma data ou não é, pelo menos, o tipo de data que o Power Pivot consegue compreender. Na verdade, é apenas texto que lê como uma data. Para criar uma relação entre uma coluna de data na tabela de factos e uma coluna de data numa tabela de datas, ambas as colunas têm de ser do tipo de dados Data.

Normalmente, quando tenta alterar o tipo de dados de uma coluna de datas que são do tipo de dados de texto para um tipo de dados de data, o Power Pivot consegue interpretar as datas e convertê-las automaticamente num verdadeiro tipo de dados de data. Se o Power Pivot não conseguir fazer uma conversão de tipo de dados, receberá um erro de tipo de não lógica.

No entanto, ainda pode converter as datas num verdadeiro tipo de dados de data. 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 em conjunto 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 denominada DateTime. Os valores são apresentados da forma que é apresentado:

Se olharmos para o Tipo de Dados no separador Base do grupo Formatação do Power Pivot, vemos que é o tipo de dados Texto.

Não é possível criar uma relação entre a coluna DateTime 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,é apresentado um erro de tipo de incomparável:

Neste caso, o Power Pivot não conseguiu converter o tipo de dados de texto para data. Ainda podemos utilizar esta coluna, mas para torná-la um verdadeiro tipo de dados de data, precisamos de criar uma nova coluna que a analisar e criar de novo num valor que o Power Pivot possa criar um tipo de dados Data.

Lembre-se de que, na secção Trabalhar com Tempo anteriormente neste artigo; a menos que seja necessário que a sua análise se transforme num nível de precisão de hora do dia, deve converter as datas na sua tabela de factos num nível de precisão diariamente. Neste caso, queremos que os valores na nossa nova coluna sejam ao nível de precisão do dia (excluindo a hora). Podemos converter os valores na coluna DateTime 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), SETA.SE.S([DateTime],6,2), SETA.SE.S([DateTime],9,2))

Isto dá-nos uma nova coluna (neste caso, denominada Data). O Power Pivot deteta até os valores a ser datas e define o tipo de dados automaticamente como Data.

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

=DATA(ESQUERDA([DateTime],4), SETA.SE.S([DateTime],6,2), SETA.SE.S([DateTime],9,2)) +

TEMPO(SETA.SE.S([DateTime],12,2), SETA.SE.S([DataData],15,2), SETA.SE.S([DataData],18,2))

Agora que temos uma coluna Data do tipo de dados Data, podemos criar uma relação entre a mesma e uma coluna de data numa data.

Recursos adicionais

Datas no Power Pivot

Cálculos no PowerPivot

Guia de Introdução: Noções Básicas sobre a linguagem DAX em 30 Minutos

Referência de Expressões de Análise de Dados

Centro de Recursos DAX