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 e, em seguida, copiá-la para uma nova tabela no Modelo de Dados. Isto é realmente muito fácil de fazer e dá-lhe muita flexibilidade.

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

Adicionar 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 num intervalo de datas. Também é necessário criar uma relação entre a tabela de factos e a tabela de datas. No entanto, essa coluna de data única com uma linha para cada dia não é útil ao analisar por datas num relatório de Tabela Dinâmica ou do Power View. Pretende que a tabela de datas inclua colunas que o ajudem a agregar os seus dados para um intervalo ou grupo de datas. Por exemplo, poderá querer somar os valores das vendas por mês ou trimestre ou pode criar uma medida que calcula o crescimento ao longo do ano. Em cada um destes casos, a tabela de datas precisa de colunas de ano, mês ou trimestre que lhe permitam agregar os seus dados para esse período.

Se tiver importado a tabela de datas 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 verdade 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 já trabalhou com funções de data e hora em fórmulas do Excel, é provável que esteja familiarizado com as Funções de Data e Hora. Embora estas funções sejam semelhantes às suas equivalentes no Excel, existem algumas diferenças importantes:

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

• Podem utilizar valores de uma coluna como 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. Vamos utilizar várias destas funções para criar colunas para Ano, Trimestre, MêsFiscal, etc.

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

• DATA

• DATA.VALOR

• DIA SEGUINTE

• DATA DE EDATE

• EOMONTH

• HORA

• MINUTO

• MÊS

• AGORA

• SEGUNDO

• HORA

• VALORDESEMPRESA

• HOJE

• DIA DA SEMANA

• WEEKNUM

• ANO

• YEARFRAC

Também pode utilizar muitas outras funções DAX nas suas fórmulas. Por exemplo, muitas das fórmulas descritas aqui utilizam 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 DAX, consulte a secção Recursos Adicionais mais adiante neste artigo.

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

Os exemplos seguintes descrevem fórmulas utilizadas para criar colunas adicionais numa tabela de 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([date],"mmmm")

Nesta fórmula, para obter o nome do mês, utilizamos 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 o nome do nosso mês mostre todos os carateres, pelo que utilizamos "mmmm". O nosso resultado tem o seguinte aspeto:

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

Dia da Semana

=FORMAT([date],"ddd")

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

Tabela Dinâmica de Exemplo

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

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 totais de vendas para o dia de Ano Novo ao adicionar um campo Feriado a uma Tabela Dinâmica, como uma segmentação de dados ou filtro. Noutros casos, poderá querer excluir essas datas de outras colunas de data ou de uma medida.

Incluir feriados ou dias especiais é bastante simples. Pode criar uma tabela no Excel que tenha as datas que pretende incluir. Em seguida, pode copiar ou utilizar Adicionar ao 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 LOOKUPVALUE 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 Feriado e utilizamos uma fórmula como esta:

=VALORPROV.PROC(Feriados[Feriados];Feriados[data],Calendário[data])

Vamos analisar esta fórmula com mais cuidado.

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

=VALORPROV.PROC(Feriados[Feriados];Feriados[data],Calendário[data])

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

=VALORPROV.PROC(Feriados[Feriados];Feriados[data],Calendário[data])

Por fim, especificamos a coluna na nossa tabela Calendário que tem as datas que queremos procurar na tabela Feriados . Esta é, naturalmente, a coluna Data na tabela Calendário .

=VALORPROV.PROC(Feriados[Feriados];Feriados[data],Calendário[data])

A coluna Feriados 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 alimentar, reportam frequentemente 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, etc. Cada período contém o mesmo número de dias e, normalmente, os feriados serão incluídos no mesmo período de cada ano. Pode optar por iniciar um período em qualquer dia da semana. Tal como acontece com 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 7/1.

Period

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

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

Vamos analisar esta fórmula com mais cuidado.

Em primeiro lugar, criamos uma fórmula que devolve um valor da coluna Semana como um número inteiro, da seguinte forma:

=INT([Semana])

Em seguida, adicione 3 a esse valor, da seguinte forma:

=INT([Semana]+3)

Em seguida, divida o resultado por 4, da seguinte forma:

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

O resultado tem o seguinte aspeto:

Ano Fiscal do Período

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

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

Vamos analisar esta fórmula com mais cuidado.

Em primeiro lugar, criamos uma fórmula que devolve um valor de Period e adiciona 12:

= ([Período]+12)

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

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

Adicionamos 2010, porque é 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 devolver um número inteiro, quando dividido por 13, da seguinte forma:

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

O resultado tem o seguinte aspeto:

Período em Ano Fiscal

Este valor devolve o número do período, 1 a 13, começando pelo primeiro Período completo (a partir de 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 numa linguagem que compreendemos melhor. Esta fórmula indica, divida o valor de [Período] por 13 para obter um número do período (1-13) no ano. Se esse número for 0, devolva 13.

Primeiro, criamos uma fórmula que devolve o resto do valor de Período por 13. Podemos utilizar as funções MOD (Matemática e Trigonometria) da seguinte forma:

=MOD([Período];13)

Isto, na maior parte das vezes, dá-nos o resultado que queremos, exceto quando o valor de Período é 0, porque essas datas não se enquadram no primeiro ano fiscal, como nos primeiros cinco dias da nossa tabela de datas calendário de exemplo. Podemos tratar disto com uma função SE. Caso o nosso resultado seja 0, devolvemos 13, da seguinte forma:

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

O resultado tem o seguinte aspeto:

Múltiplas relações

Em alguns casos, pode ser necessário criar múltiplas relações ou criar múltiplas tabelas de datas. Por exemplo, se existirem vários campos de data na tabela de factos Vendas, como DateKey, ShipDate e ReturnDate, todos podem ter relações com o campo Data na tabela de datas do Calendário, mas apenas um deles 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 serviria 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 com o nome Total de Vendas, 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.

Esta tabela dinâmica direta funciona corretamente porque queremos somar o total de vendas até à data de transação em 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 de datas calendário.

Relações inativas

E se quisísse somar as vendas totais não por data de transação, mas por data de envio? Precisamos de uma relação entre a coluna DataDeEnvio na tabela Vendas e a coluna Data na tabela Calendário. Se não criarmos essa relação, as nossas agregações baseiam-se sempre na data de transação. No entanto, podemos ter múltiplas relações, embora apenas uma possa estar ativa e, uma vez que a data de transação é a mais importante, obtém a relação ativa com a tabela Calendário.

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

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

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Esta fórmula indica simplesmente: Calcule uma soma para SalesAmount, mas filtre utilizando a relação entre a coluna DataDeEnvio 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 Envio em VALORES e Ano Fiscal e Trimestre Fiscal em LINHAS, vemos o mesmo Total Geral, mas todos os outros montantes de soma para o ano fiscal e o trimestre fiscal são diferentes porque se baseiam na data de envio e não na data de transação.

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

Várias tabelas de datas

Outra forma de trabalhar com múltiplas colunas de data na tabela de factos é criar várias tabelas de datas e criar relações ativas separadas entre as mesmas. Vejamos novamente o exemplo da tabela Vendas. Temos três colunas com datas nas quais podemos querer agregar dados:

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

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

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

Lembre-se de que o campo DateKey com a data de transação é o mais importante. Faremos a maioria das nossas agregações com base nestas datas, pelo que iremos certamente querer uma relação entre a mesma 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 medida especiais, podemos criar tabelas de datas adicionais para data de envio e data de devolução. Em seguida, podemos criar relações ativas entre as mesmas.

Neste exemplo, criámos outra tabela de datas denominada ShipCalendar. Isto, naturalmente, também significa criar colunas de data adicionais e, uma vez que estas colunas de data estão numa tabela de datas diferente, queremos dar-lhes um nome que as difere das mesmas colunas na tabela Calendário. Por exemplo, criámos colunas denominadas ShipYear, ShipMonth, ShipQuarter, etc.

Se criarmos a nossa tabela dinâmica e colocarmos a nossa medida Total sales em VALUES e ShipFiscalYear e ShipFiscalQuarter em ROWS, vemos os mesmos resultados que vimos quando criámos uma relação inativa e um campo calculado Total de Vendas Totais especiais por Data de Envio.

Cada uma destas abordagens requer uma consideração cuidadosa. Ao utilizar múltiplas relações com uma única tabela de datas, poderá ter de criar medidas especiais que transitem relações inativas através da função USERELATIONSHIP. Por outro lado, a criação de múltiplas tabelas de datas pode ser confusa numa Lista de Campos e, uma vez que tem mais tabelas no Modelo de Dados, será necessária mais memória. Experimente o que funciona melhor para si.

Como: Definir a propriedade Tabela de Datas

1. Na janela do 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çã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 facto, uma data ou pelo menos não é o tipo de data que o Power Pivot consegue compreender. É apenas texto que se lê como uma data. Para criar uma relação entre uma coluna de data na tabela de factos e uma coluna de data numa tabela de 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 tipo de dados de texto para um tipo de dados de data, o Power Pivot pode interpretar as datas e convertê-la automaticamente num tipo de dados de data verdadeiro. Se o Power Pivot não conseguir efetuar uma conversão de tipo de dados, obterá um erro de correspondência de tipo.

No entanto, ainda pode converter as datas num tipo de dados de data verdadeiro. 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 concatenar novamente de uma forma que o Power Pivot possa ler como uma data verdadeira.

Neste exemplo, importámos uma tabela de factos denominada Vendas para o Power Pivot. Contém uma coluna denominada DateTime. Os valores são apresentados da seguinte forma:

Se observarmos o Tipo de Dados no separador Base do grupo Formatação do Power Pivot, vemos que se trata de Tipo de dados de texto.

Não conseguimos criar uma relação entre a coluna DateTime e a coluna Date na nossa tabela de datas porque os tipos de dados não correspondem. Se tentarmos alterar o tipo de dados para Data, obtemos um erro de correspondência de tipo:

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

Lembre-se, na secção Trabalhar com o tempo anteriormente neste artigo; a menos que seja necessário que a sua análise seja para um nível de precisão de hora do dia, deve converter datas na sua tabela de factos para um nível de precisão diário. Com isso em mente, queremos que os valores na nossa nova coluna estejam no nível de precisão diária (excluindo o tempo). Ambos podemos converter os valores na coluna DateTime num tipo de dados de data e remover o nível de tempo de precisão com a seguinte fórmula:

=DATA(ESQUERDA([DateTime];4), MID([DateTime];6;2), MID([DateTime],9,2))

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

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

=DATA(ESQUERDA([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 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 da DAX