Para fazer cálculos envolvendo datas no Power BI é necessário utilizar a linguagem DAX. Para isto existem, dentro desta linguagem, as funções de inteligência de tempo. Elas servem para trazer resultados de ano até o momento (year to date), ano versus ano (yoy), último dia do mês, última data disponível, etc.

Além de alguns cenários utilizando funções de inteligência de tempo no Power BI você aprenderá também sobre a opção “marcar como tabela de datas” que foi incorporada no Power BI na atualização de fevereiro de 2018. Entender o papel desta configuração feita nas tabelas que tem datas.

Baixe o arquivo do Power BI Desktop que oferecemos abaixo com todas as funções que falamos neste artigo. São exemplos práticos de quase todas as funções de inteligência de tempo disponíveis na linguagem DAX. Além de conseguir acompanhar o que falamos neste post sobre estas funções você terá também um local de consulta rápida sobre estas funções no seu PC. Assim poderá relembrar e tirar dúvidas no momento que quiser. Baixar o arquivo aqui.

Utilize o índice abaixo para navegar neste artigo. Por ele vá direto à função que deseja aprender.

Funções de inteligência de tempo em DAX

A linguagem DAX disponibiliza um grande número de funções de inteligência de tempo. Veja todas neste link. Estas funções podem ser divididas em duas categorias:

• Funções que retornam valores escalares sem necessidade da função CALCULATE;
• Funções que retornam uma tabela, que será usada como um filtro numa fórmula CALCULATE.

Um exemplo do primeiro grupo é a função TOTALYTD. Este grupo, na verdade, apenas simplifica uma fórmula que utilizaria CALCULATE, trocando por uma função de inteligência de tempo. Exemplo de uma expressão utilizando TOTALYTD.

No segundo grupo, utiliza-se a função CALCULATE em conjunto. De fato, a função acima pode ser obtida também conforme abaixo, mas utilizando DATESYTD como um filtro.

Na fórmula acima é aplicado um filtro ao campo “CalendarioDAX[Date]”. Este substitui filtros existentes nesta coluna. Na prática a função DATESYTD por ser substituída pela função FILTER. A fórmula anterior corresponde a mesma fórmula a seguir.

Entenda mais sobre a função FILTER no artigo “5 funções DAX para usar muito no Power BI”.

Observe na figura abaixo que o resultado é o mesmo. Os três contextos utilizados trazem o mesmo valor, porque são formas diferentes para buscar um mesmo resultado. Em duas utilizamos funções de inteligência de dados temporais, nos dois grupos que destacamos no início deste tópico.

Passamos agora a falar das funções de inteligência de tempo dividas pelos resultados que retornam.

 

Funções que retornam uma única data

Como nesta categoria as funções retornam uma única data, podem ser utilizadas como argumentos para outras funções. Abaixo colocamos alguns exemplos de funções deste grupo. De qualquer forma citamos todas disponíveis no texto.

FIRSTDATE

Retorna a primeira data da coluna de datas utilizadas no contexto atual.

Estrutura da fórmula: FIRSTDATE (<coluna_datas>)

Exemplo:

LASTDATE

Retorna a última data da coluna de datas utilizadas no contexto atual.

Estrutura da fórmula: LASTDATE (<coluna_datas>)

Exemplo:

As duas funções de inteligência de dados temporais acima são úteis em casos que deseje encontrar a primeira ou a última data em que foi feita uma venda, por exemplo. Dá para especificar qual produto, qual valor e mais, utilizando juntamente a função CALCULATE e/ou FILTER.

 

STARTOFMONTH

Retorna a primeira data do mês para uma coluna de datas utilizadas dentro do contexto atual.

Estrutura da fórmula: STARTOFMONTH (<coluna_datas>)

Exemplo:

 

ENDOFMONTH

Retorna a última data do mês para uma coluna de datas utilizadas dentro do contexto atual.

Estrutura da fórmula: ENDOFMONTH (<coluna_datas>)

Exemplo:

Além das duas funções acima temos ainda as funções STARTOFQUARTER e ENDOFQUARTER, que, nos mesmos moldes, são para datas em trimestres. Temos também as funções STARTOFYEAR e ENDOFYEAR que tratam o contexto na visão de ano.

Ao todo são 10 funções de inteligência de tempo que retornam um único valor de data, restou listar entre estas as funções FIRSTNOBLANK e LASTNOBLANK, que utilizadas em colunas de datas retorna valores (primeiro ou último) que não estão em branco.

Abaixo uma imagem dos resultados destas funções. Elas estão no arquivo que você fez o download acima (se não fez, faça agora pois ele contém vários exemplos das funções de inteligência de tempo em aplicações práticas).

 

Funções que retornam uma tabela de datas

Dentre às funções de inteligência de tempo há aquelas que retornam tabelas de datas. Estas, normalmente, são utilizadas como um argumento de filtro na função CALCULATE.

Estas funções, citaremos exemplos de algumas, mas ao todo são oito nesta situação, retornam um resultado a partir de uma coluna de data especificada antes.

 

PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER e PREVIOUSYEAR

Vamos utilizar a função PREVIOUSDAY como principal exemplo para se basearem. Esta função retorna uma tabela que contém uma coluna com todas as datas que representam o dia anterior à primeira data da coluna de datas na tabela Calendário no contexto atual.

Estrutura da fórmula: PREVIOUSDAY (<coluna_datas>)

Exemplo:

Neste exemplo a função retornará o valor de vendas do dia anterior em relação à data atual. Para exemplificar, para o dia 05/08/2018 a função retornará as vendas do dia 04/08/2018. No arquivo disponibilizado temos um exemplo prático desta função e de todas logo abaixo, para você entender melhor. Não deixe de baixa-lo logo no início deste post.

Além desta função temos também as funções PREVIOUSMONTH, PREVIOUSQUARTER e PREVIOUSYEAR. Estas outras três funções tem a mesma estrutura e lógica da PREVIOUSDAY, só representam contextos diferentes.

PREVIOUSMONTH

Retorna uma tabela que contém uma coluna com todas as datas que representam o mês anterior à primeira data da coluna de datas na tabela. Esta função retorna a soma de valores do mês fechado. Ou seja, caso a linha contenha uma data do mês de agosto retornará a soma das vendas total do mês de julho.

PREVIOUSQUARTER

Retorna uma tabela que contém uma coluna com todas as datas que representam o trimestre anterior à primeira data da coluna de datas na tabela. Esta função retorna a soma de valores do trimestre fechado. Ou seja, caso a linha contenha uma data do mês de agosto retornará a soma das vendas de todo trimestre anterior, no caso a soma dos meses abril, maio e junho.

PREVIOUSYEAR

Retorna uma tabela que contém uma coluna com todas as datas que representam o ano anterior à primeira data da coluna de datas na tabela. Esta função retorna a soma de valores do ano fechado. Ou seja, caso a linha contenha uma data do mês de agosto/17 retornará a soma das vendas de todo ano anterior, no caso a soma de todos os meses de 2016.

 

NEXTDAY, NEXTMONTH, NEXTQUARTER e NEXTYEAR

Estas funções de inteligência de tempo tem a mesma lógica das funções citadas acima que começam com PREVIOUS. Essa palavra é substituída por NEXT, que significa próximo. Assim estas funções retornam uma tabela que contém uma coluna com todas as datas que representam um dia, um mês, um trimestre ou um ano seguinte à primeira data da coluna de datas.

Utilizaremos o exemplo da função NEXTDAY para ilustrar, mas as outras seguem a mesma estrutura. No arquivo de Power BI Desktop disponibilizado deste artigo tem todos as funções exemplificadas. Esta função retorna o resultado do dia posterior à primeira data da coluna.

Estrutura da fórmula: NEXTDAY (<coluna_datas>)

Exemplo:

As explicações para as funções NEXTMONTH, NEXTQUARTER e NEXTYEAR são as mesmas de suas correspondentes PREVIOUS acima. Apenas substitua a palavra anterior por seguinte nas explicações.

 

DATESMTD, DATESQTD, DATESYTD e SAMEPERIODLASTYEAR

Estas funções de inteligência de tempo, ao invés de calcular um período anterior ou próximo, elas calculam o conjunto de datas no contexto. Melhor dizendo o resultado será do mês até o dia (DATESMTD), ou do trimestre até o dia (DATESQTD) ou do ano até o dia (DATESYTD). Todas essas funções executam seus cálculos usando a última data no contexto atual.

Para ilustrar utilizaremos a função DATESMTD, que retorna uma tabela que contém uma coluna de datas desde o início do mês até o dia atual no contexto.

Estrutura da fórmula: DATESMTD (<coluna_datas>)

Exemplo:

As funções DATESQTD e DATESYTD seguem a mesma lógica acima, mas retornam o total do trimestre ou ano respectivamente.

Já a função SAMEPERIODLASTYEAR requer um conjunto contínuo de datas, sem que as datas sejam contínuas esta função retornará um erro. Retorna uma tabela que contém uma coluna de datas com o atraso de um ano em relação às datas na coluna atual do contexto.

 

DATEADD, DATESBETWEEN, DATESINPERIOD e PARALLELPERIOD

Estas últimas quatro funções na categoria de funções que retornam uma tabela de data são um pouco mais complexas, mas também mais poderosas. Estas funções são utilizadas para deslocar-se do conjunto de datas de forma mais dinâmica, obedecendo o contexto atual das datas.

DATEADD

Retorna uma tabela que contém uma coluna de datas, adiantadas ou atrasadas no tempo conforme o número especificado de intervalos das datas no contexto atual. É possível substituir a função SAMEPERIODLASTYEAR que descrevemos acima pela fórmula abaixo. Este é apenas um dos exemplos da utilização desta função. Ela serve para adiantar ou atrasar os cálculos para dias, meses, trimestres e o caso do exemplo, anos.

DATESBETWEEN

Calcula um conjunto de datas entre uma data de início e uma data de término especificas nos argumentos. Esta função, bem como as próximas duas deslocam algum número de intervalo de tempo do contexto atual. Elas facilitam para deslocar os intervalos em dias, meses, trimestres e ano. Com elas é possível voltar 4 meses, adiantar um ano, voltar 2 semanas (14 dias), etc. No exemplo abaixo somamos os resultados de vendas nos primeiros 15 dias do mês.

DATESINPERIOD

Da mesma forma da função acima esta função desloca um número de intervalo de tempo do contexto atual. No exemplo abaixo fazemos o cálculo somando as vendas de 15 dias antes da data atual do contexto. Desta forma teremos as vendas -15 dias para cada data que apareça no contexto.

PARALLELPERIOD

Esta função retorna uma tabela que contenha uma coluna de datas que representa um período paralelo na coluna de datas no contexto atual, com as datas deslocadas em vários intervalos no presente ou no passado. A função PARALLELPERIOD é bem parecida à função DATEADD, exceto pelo fato de que PARALLELPERIOD sempre retorna períodos completos. A função DATEADD retorna períodos parciais. Por exemplo, se você tiver uma seleção de datas começando em 05/04/2017 e terminando em 16/04/2017 e quiser deslocar essa seleção um mês à frente, a função PARALLELPERIOD retornará todas as datas do próximo mês (de 1º de maio a 31 de maio); no entanto, se DATEADD for usada, o resultado só incluirá datas de 05 a 16 de maio. Na fórmula abaixo, retorna a soma das vendas de todo o mês seguinte à data no contexto.

Abaixo uma imagem dos resultados de todas as funções deste tópico que disponibilizamos no arquivo com todos os exemplos das funções de inteligência de tempo em aplicações práticas. Não deixe de baixar o arquivo para um melhor entendimento do que falamos aqui. Você pode baixar utilizando os links no início ou no fim deste artigo.

 

Funções que avaliam expressões ao longo de um período de tempo

Este tipo de funções de inteligência de dados temporais avaliam uma expressão durante um período de tempo específico. São funções para simplificar e deixar suas expressões mais curtas. São todas fórmulas que podem ser substituídas pela função CALCULATE juntamente com outra função de inteligência de tempo.

 

TOTALMTD, TOTALQTD e TOTALYTD

Estas três fazem a mesma coisa, mas em período de tempos diferentes.

Como exemplo analisaremos a função TOTALMTD.

Estrutura da fórmula: TOTALMTD (<expressão>; <coluna_datas>)

Exemplo:

Esta fórmula traz o mesmo resultado da fórmula abaixo.

Mas é melhor utilizar a primeira por ser uma fórmula reduzida e com isso utilizamos menos recursos de memória. No exemplo acima, se colocar num gráfico diário, esta função irá somar as vendas acumulando dia a dia um novo valor. No arquivo que disponibilizamos você conseguirá ver este exemplo e outros na prática.

As funções TOTALQTD e TOTALYTD tem a mesma estrutura, mas fazem o acúmulo dos resultados por trimestre e por ano respectivamente. Temos um exemplo da TOTALYTD logo no começo deste artigo.

Dentro desta categoria existem também um grupo de funções que trabalham com balanços de abertura e fechamento. São funções com um apelo específico para área contábil e financeira, não vamos tratar em detalhes destas funções aqui. Coloquei abaixo o link direto ao site da Microsoft onde poderá saber os detalhes técnicos destas funções.

OPENINGBALANCEMONTH (<expressão>, <coluna_datas>)

OPENINGBALANCEQUARTER (<expressão>, <coluna_datas>)

OPENINGBALANCEYEAR (<expressão>, <coluna_datas>)

CLOSINGBALANCEMONTH (<expressão>, <coluna_datas>)

CLOSINGBALANCEQUARTER (<expressão>, <coluna_datas>)

CLOSINGBALANCEYEAR (<expressão>, <coluna_datas>)

Apenas explicando um pouco dos conceitos destas funções. Para alguns as explicações vão parecer meio obvias, mas é importante nivelar todos num mesmo patamar de conhecimento. As funções que começam com OPENING, de abertura, é baseado na última data do período anterior. Aquelas que começam com CLOSING, de fechamento, tratam a última data do período atual. O balanço de abertura para qualquer período é o mesmo que o balanço de fechamento do período anterior.

Há alguma das funções citadas e não detalhadas que queiram um maior detalhamento? Se sim deixe um comentário ao final neste artigo que daremos mais detalhes da função de inteligência de dados temporais solicitada.

Fonte: Office-Microsoft

Marcar como tabela de data. Entenda melhor.

Quando se aplica a configuração “Marcar como tabela de data” a uma tabela, o DAX automaticamente inclui uma função ALL sobre esta tabela em cada fórmula CALCULATE com um filtro sobre coluna utilizado dentro da expressão. Por exemplo, se marcar uma tabela de nome “Calendário” como tabela de data utilizando a coluna “Date” para isto, dá para escrever a seguinte expressão.

Automaticamente a lógica do DAX irá incluir uma função ALL para a tabela “Calendário”, removendo qualquer filtro das outras colunas da mesma tabela. Por trás a expressão ficaria assim.

Caso esteja utilizando a tabela Calendário com uma coluna de tipo de Data como chave primária para os relacionamentos, mesmo se não aplicar “Marcar como tabela de data” as funções de inteligência de tempo funcionarão normalmente. Isto porque é uma coluna de tipo Data no relacionamento.

Caso o relacionamento da tabela Calendário não seja a partir de uma coluna do tipo Data, aí sim será necessário utilizar a configuração “Marcar como tabela de data” para as funções de inteligência de tempo funcionarem. Ou então pode incluir manualmente a função ALL nas funções que irá utilizar em conjunto para que funcionem adequadamente.

Conclusão

As funções de inteligência do tempo são um dos grandes trunfos da linguagem DAX. Elas dão uma nova perspectiva no trabalho com datas dentro do Power BI ou Power Pivot, deixam esta atividade realmente mais inteligente.

Possibilitam comparativos e cálculos que não é possível com fórmulas de excel ou outras ferramentas de mercado. Deixam o Power BI ainda mais power.

Não deixe de baixar o arquivo do Power BI Desktop com a aplicação prática de todas as funções que falamos neste artigo. Com este arquivo é possível consultar a qualquer tempo como as funções de inteligência de dados temporais funcionam e com isso lhe garantir resolver suas dúvidas rapidamente. Aproveite e baixe o arquivo aqui.

 

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto. Ajude-nos também a espalhar bons conteúdos sobre o Power BI.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!