À primeira vista, o título parece estranho.
Só para quem nunca viu código deste à solta em modelos de Power BI… (e não só!)
A questão é simples:
quero calcular os “valores do ano anterior”
Simples, diria eu… não coloquemos de lado a capacidade que alguns de nós temos em complicar uma coisa que poderia ser simples. A mim nunca me aconteceu, nunca compliquei nada nem padeço de overengineering 🙂
Começamos com um daqueles modelos minimalistas que tanto adoro.
Esta escolha tem uma razão, simplifica o que vos quero mostrar mais à frente.
Temos uma métrica “Val” definida apenas com uma soma da coluna “valor”.
Agora imaginem que abrem um pbix e vêm este código para implementar “Val do ano anterior”
_1__ Val Y-1 =
var __max_data = max('Dim Calendário'[SK_Data])
var __max_ano = YEAR(__max_data)
var __max_mes = MONTH(__max_data)
var __max_dia = DAY(__max_data)
var __max_data_ano_anterior = DATE(__max_ano -1, __max_mes, __max_dia)
var __min_data = min('Dim Calendário'[SK_Data])
var __min_ano = YEAR(__min_data)
var __min_mes = MONTH(__min_data)
var __min_dia = DAY(__min_data)
var __min_data_ano_anterior = DATE(__min_ano -1, __min_mes, __min_dia)
return
calculate(
[Val],
DATESBETWEEN('Dim Calendário'[SK_Data], __min_data_ano_anterior, __max_data_ano_anterior)
)Temos variáveis a guardar a data máxima em contexto, a separar depois o ano, mês e dia, criar depois uma data com o mesmo dia e mês, retirando 1 ao ano. A mesma coisa para a data mínima, rematado com um CALCULATE para calcular (pois claro) a métrica original para as datas entre a nova data mínima e máxima.
Parece um caso típico de matar moscas com bazuca, mas o mais importante é:
Será que funciona?
Tudo parece bonitinho.
Para dia 1 de janeiro de 2021 o “valor do ano anterior” é 1.
Para dia 31 de dezembro de 2020 também confere, 31.
Ora… está certo, siga a marinha!
Esta fórmula, entre todos os problemas que tem, tem dois que saltam mais à vista.
Vamos atacar o primeiro problema.
Se eu selecionar apenas dois dias, mas que não são seguidos?
A soma dos valores em contexto é 6… e para o ano anterior?
O que vemos no multi-row card em cima? 6 para as datas em contexto, 15 para as do ano anterior?
Rapidamente se percebe o que está a acontecer.
De dia 1 a dia 5 o total é 15.
Vamos relembrar o que o código implementado está a fazer?
Vamos lá devagarinho, em português:
Primeiro guarda-me a maior data em contexto: 5 de dezembro de 2020.
Agora guarda-me o ano, mês e dia: ano 2020, mês dezembro e dia 5.
Depois cria-me uma data com o mesmo dia, mês e com o ano anterior: 5 de dezembro de 2019.
Faz-me o mesmo mas para a menor data em contexto: 1 de dezembro de 2020.
Guarda então o ano, mês e dia: ano 2020, mês dezembro e dia 1.
Cria-me outra data para o ano anterior: 1 de dezembro de 2019.
Agora “calcula-me” a métrica da soma para datas entre a menor data e a maior data: soma-me os valores para as datas entre dia 1 de dezembro de 2019 e 5 de dezembro de 2019.
Se faz favor, que cá também se usa e…
Resultado: 1+2+3+4+5=15.
“O Power BI está a dar o resultado errado”.
É o famoso “está a fazer o que lhe pedimos para fazer”.
E o segundo problema?
Para quem se lembra, este dia 29 de fevereiro foi complicado para os lados do Azure.
Mas… o que tem de especial 29 de fevereiro?
Todos sabemos… só acontece em ano bissexto, de 4 em 4 anos.
Aliás, um dos meus melhores amigos leva todos os anos com uma de duas piadas: “Então… este ano não fazes anos?” ou (a que vai levar para o ano) “Então… como é que te sentes agora que já tens 12 anos?”
É parvo, eu sei, mas é a única pessoa que conheço a quem posso fazer estas piadas secas…
Para quem tem interesse nos pormenores, aqui fica o link para a excelente explicação que a Microsoft deu aos seus clientes.
Para quem não se interessa pelos pormenores, uma data de validade de 1 ano num certificado era calculada “adicionando 1” ao ano. Claro que, chegando ao dia 29 de fevereiro de 2012, o certificado era emitido com uma data de validade que não era válida (29 de fevereiro de 2013), logo o certificado não era válido. Rapidamente as coisas se começaram a complicar e o resultado final foram várias horas de downtime.
E o que isto tem importância para o que estamos aqui a fazer?
É que vamos ter o mesmo problema quando temos de “fazer as contas” ao dia 29 de fevereiro.
Vamos ver o que acontece?
Para este caso, o que vai acontecer aos totais se escolhermos dia 29 de fevereiro e dia 1 de março?
Temos um total para o ano atual de 30. Soma 29 de dia 29, 1 de dia 1, total 30. Confere!
Agora para o ano anterior… 1? Mas… como 1?
Mais uma vez, vamos devagarinho e em português.
Para dia 29 de fevereiro o “valor do ano anterior” é 1. Lembram-se de vos ter dito que o modelo estava feito para ser mais fácil de entender o que estava a acontecer? Se o “valor do ano anterior” é 1, o DAX está a dar-nos o valor de 1 de março de 2019 como o “valor do ano anterior” de 29 de fevereiro de 2020.
O “total deste ano” é o total do dia 29 de fevereiro de 2020 e do dia 1 de março de 2020. Já vimos, é 30.
O “total do ano anterior” pode parecer estranho, mas é o “total do dia 1 de março de 2019 e… o total do dia 1 de março de 2019”, ou seja, “o total do dia 1 de março de 2019”. Resultado? 1. Está certo? Bem… eu diria que sim, foi isso que pedimos para o Power BI fazer!
Vamos a outro exemplo?
Vamos para um ano que não é bissexto mas em que o ano anterior é bissexto, 2021.
Para dia 28 de fevereiro de 2021 temos como valor 28 e como “valor do ano anterior” 28.
Para dia 1 de março de 2021 temos como valor 1 e como “valor do ano anterior” 1. Tudo parece bem.
Mas… já olharam para os totais?
29 para a soma do ano atual, 28+1=29, confere!
Para o ano anterior… 58? Agora é apanhámos um bug, isto “crashou” (ou como diz o meu homem de 11 anos cá em casa, “bugou”). Não é possível.
Vamos lá ver como é que nos aparece 58 no total.
Primeiro, temos o “valor do ano anterior de 28 de fevereiro de 2021”. Já vimos que, neste caso, esse valor é o “valor de 28 de fevereiro de 2020”, que é 28. Primeira parcela está descoberta.
Segundo, temos o “valor do ano anterior de 1 de março de 2021”, que também já vimos que é o “valor de 1 de março de 2020”. Segunda parcela está descoberta.
Até agora temos 28+1=29… O que falta para termos 58?
Se fizermos a diferença entre os dois valores começamos a ter uma ideia do que se está a passar.
Não acreditam?
Vamos lá: 58-29=29. O que quer isto dizer?
Será que o DAX “duplicou” o resultado? Ou é algo bem menos sinistro, como por exemplo… um dia daqueles que só acontece de 4 em 4 anos e que não está no contexto atual mas no contexto do ano anterior?
Em qual apostam?
Explicando melhor a terceira parcela:
Para o “dia anterior do dia 28 de fevereiro de 2021” temos, como já vimos, o “dia de 28 de fevereiro de 2020”.
Para o “dia anterior de 1 de março de 2021” temos o “dia 1 de março de 2020”.
E o que acontece ao “dia de 29 de fevereiro de 2020”, será que ele faz parte da conta?
Sim! Por mais estranho que pareça, o “dia 29 de fevereiro de 2020” é o “dia do ano anterior do dia 1 de março de 2021”.
Ai… ainda agora disse que o “dia 1 de março de 2020” é o “dia do ano anterior de dia 1 de março de 2021”, agora estou a dizer que também o “dia 29 de fevereiro de 2020” é o “dia do ano anterior de dia 1 de março de 2021″… já estás balhelhas, ó António!
Mas… é mesmo assim!
Tanta coisa e ainda não chegámos ao segundo problema
Pois é, o segundo problema não é este. Os cálculos estão certos, apesar de parecerem estranhos.
Então… qual é o problema?
Já vimos que o Power BI, para este caso, quando estamos no dia 29 de fevereiro considera que a “mesma data no ano anterior” é 1 de março.
Mas… será?
Vamos questionar o nosso amigo ChatGPT?
Não parece importante, mas é.
Vamos criar a métrica que deveria ter sido criada logo de raiz?
Vamos a isso.
Queremos os “valores do ano anterior”? Simples, basta pedir com jeitinho os “valores do ano anterior”.
_2__ Val Y-1 =
calculate([Val], sameperiodlastyear('Dim Calendário'[SK_Data]))E se colocarmos esta métrica nos visuais que já temos?
Agora para dia 29 de fevereiro de 2020 temos como “valor do ano anterior” 28, o “valor de dia 28 de fevereiro de 2019”, como mandam as regras.
Para dia 1 de março de 2020 temos o “valor do ano anterior” 1, também como esperado o “valor de dia 1 de março de 2019”.
Resultado do ano anterior? 28+1=29.
Nem tudo o que parece, é. Para este segundo caso as coisas parecem que continuam mal. Afinal, 58 era o valor que tínhamos com a métrica anterior que já mostrámos estar errada.
Pois é… mas este valor está certo. Como é isso possível?
A única diferença nestas duas métricas é como o “dia 29 de fevereiro de 2020” está a entrar nos cálculos.
Antes era o “dia do ano anterior de 1 de março de 2021”, agora é o “dia do ano anterior de 28 de fevereiro de 2021”.
E, já agora, queremos os valores do ano seguinte
Vamos criar duas métricas novas para o “ano seguinte”. Só porque sim.
A primeira com código similar à que tínhamos em primeiro lugar.
_1__ Val Y+1 =
var __max_data = max('Dim Calendário'[SK_Data])
var __max_ano = YEAR(__max_data)
var __max_mes = MONTH(__max_data)
var __max_dia = DAY(__max_data)
var __max_data_ano_anterior = DATE(__max_ano +1, __max_mes, __max_dia)
var __min_data = min('Dim Calendário'[SK_Data])
var __min_ano = YEAR(__min_data)
var __min_mes = MONTH(__min_data)
var __min_dia = DAY(__min_data)
var __min_data_ano_anterior = DATE(__min_ano +1, __min_mes, __min_dia)
return
calculate(
[Val],
DATESBETWEEN('Dim Calendário'[SK_Data], __min_data_ano_anterior, __max_data_ano_anterior)
)A segunda similar à que se fez com o SAMEPERIODLASTYEAR mas com um DATEADD para podermos “ir para a frente”.
_2__ Val Y+1 = CALCULATE([Val], DATEADD('Dim Calendário'[SK_Data], 1, YEAR))Resultados:
Quando estamos num ano bissexto, a primeira métrica volta a comportar-se como esperado, indo buscar o dia 1 de março de 2021 como “o ano a seguir a 1 de março de 2020”. A segunda métrica dá o valor que esperávamos.
Para o caso de estarmos num ano que não é bissexto e apontarmos para um ano que também não é bissexto, as duas métricas dão o valor esperado de 29, porque não estamos nunca a colocar “ao barulho” nenhum dia 29 de fevereiro.
Para o caso em que estamos num ano que não é bissexto mas o “ano a seguir” é bissexto, as duas métricas voltam a dar o valor esperado de 58.
De onde vêm as parcelas?
Como já explicado no outro caso similar, de dia 28 de fevereiro de 2020, de dia 1 de março de 2020 e de dia 29 de fevereiro de 2020, na primeira métrica por causa de dia 1 de março de 2019 e na métrica “certa” por causa de dia 28 de fevereiro de 2019.
Simples, não é?
Sei que isto pode ainda meter alguma confusão.
Já sabem, não hesitem em colocar as vossas questões.
(Imagem do post de Eric Rothermel no Unsplash)
