Como mostrar o total do último período de cada ano num gráfico por ano?

[urair_oliveira]

Boa noite.

Preciso apresentar num gráfico:

Dimensão: Ano

Expressão: Total no último mês de cada ano (considerando outras seleções feitas, inclusive Mês)

Ou seja, se o usuário selecionar os meses de Julho, Agosto e Setembro, deverá apresentar o total do mês de Setembro de cada ano, mas no ano atual deverá apresentar o total de Agosto, pois hoje é 15/08/2018.

A expressão abaixo está mostrando para 2018 o total do último período (Ago/2018). Está correto, mas os anos anteriores estão zerados, apesar dos anos serem mostrados no eixo X.

=Aggr( Sum( {< Periodo={'$(=Max(Periodo))'} >} Qtde ), Ano )

Onde:

   Ano = inteiro (ex.: 2017)

   Periodo = Data com dia 1 e formato 'MMM/yyyy'

   Qtde = inteiro

Alguém sabe como corrigir esta expressão?

Por sugestão de um amigo adicionei um campo marcando registro se for o último período do ano:

If( Month([Período])=12 or [Período]=Num(MonthStart(Today())), 's', Null() ) as fULTPER

Funcionou, mas se selecionar o Mês, bagunça tudo.

Então tive que travar o mês:

Sum( {< fULTPER={'s'}, [Mês]= >} Qtde )

Gostaria de dar a liberdade pro usuário escolher o filtro que quiser, inclusive meses.

Alguém sabe o que precisa alterar na primeira expressão para atingir o objetivo esperado?

Obrigado e ótima semana.

[Clever_Anjos]

Pode postar o seu modelo de dados ou um exemplo deles em excel?

Como obter ajuda? Leia antes de postar

[urair_oliveira]

Anexei um 2 arquivos para facilitar o entendimento. Obrigado pela ajuda.

[urair_oliveira · Accepted solution]

Em conversa com o amigo Demétrius César Rodrigues ( demetriuscrodrigues‌ ), rapidamente solucionou o problema.

Muito obrigado, Demétrius por compartilhar seus conhecimentos.

Seguem as alterações necessárias para atingir o objetivo desejado:

No código:

[Clientes Ativos]:

LOAD

Date( MonthStart(DATA), 'MMM/yyyy' ) as [Período],

Month(DATA) as [Mês],

Num(Month(DATA)) as fMES,

Year(DATA) as [Ano],

    CLIENTE as Cliente,

    ATIVO as Qtde

FROM [lib://Dados/dados exemplo.xlsx]

(ooxml, embedded labels, table is Planilha1);

-----------------------------------------------------------------

No gráfico:

= aggr( if( max(Ano)=Max(all Ano),

Sum( {< fMES={'$(=Max(fMES))'} >} Qtde) + Sum( {1< fMES={'$(=Max(fMES-1))'} >} Qtde),

Sum( {< fMES={'$(=Max(fMES))'} >} Qtde)

), Ano)