DAX: Como Replicar e Acumular Valores de Anos Anteriores no Power BI

[Luiz_Mattos]

Estou tentando criar uma medida em DAX no Power BI que replique e acumule valores de um ano para o próximo. O objetivo é que, para cada ano, a medida some o valor do ano atual com o valor acumulado de todos os anos anteriores.

Por exemplo:

• Para o ano de 2021, a medida deve retornar o valor de 2021.
• Para 2022, a medida deve retornar o valor de 2021 + o valor de 2022.
• Para 2023, a medida deve retornar o valor acumulado de 2021 e 2022 + o valor de 2023.
• E assim por diante para os anos subsequentes.
  
  Estou tentando utilizar a seguinte fórmula DAX, mas ainda não consegui o resultado esperado:
  
  Repetir ano anterior = 
  VAR CurrentYear = YEAR([Data minima])
  VAR CurrentYearValue = 
      CALCULATE([Ganho total recorrente USD MRF atual], CalendarioDax[Ano] = CurrentYear)
  VAR PreviousYearValue = 
      CALCULATE([Ganho total recorrente USD MRF atual], FILTER(ALL(CalendarioDax), CalendarioDax[Ano] = CurrentYear - 1))
  VAR SameperiodLY = 
      CALCULATE([Ganho total recorrente USD MRF atual], SAMEPERIODLASTYEAR(CalendarioDax[Date]))
  RETURN 
  CurrentYearValue + PreviousYearValue + SameperiodLY
  
  

Quero que na tabela replique a mesma lógica para 2023, 2024, etc...mas com a formula atual ela para em 2022.

Alguém poderia me ajudar a ajustar essa medida para que funcione corretamente e replique os valores acumulados de ano para ano?
 

[Henrique Merola Faria]

@Luiz_Mattos experimenta aí: 

Valor Acumulado = 
CALCULATE(
     [Ganho total recorrente USD MRF atual],
     FILTER(ALL(CalendarioDAX),
            CalendarioDax[Date] < = MAX(CalendarioDax[Date])
         )
)
   

Editado Setembro 11 por Henrique Merola Faria
Correção - ")" sobrando....

[Luiz_Mattos]

55 minutos atrás, Henrique Merola Faria disse:

@Luiz_Mattos experimenta aí: 

Valor Acumulado = 
CALCULATE(
     [Ganho total recorrente USD MRF atual],
     FILTER(ALL(CalendarioDAX),
            CalendarioDax[Date] < = MAX(CalendarioDax[Date])
         )
)
   

Primeiramente, obrigado pela ajuda.

Usando sua formula, deu o seguinte resultado :

O que eu preciso é, seria que em 2022, fosse a soma do ano anterior + o ano atual ($14.068.829), e a mesma logica para 2023, 2024 etc igual a tabela abaixo:

 

 

Editado Setembro 11 por Luiz_Mattos

[Edson Igari]

Veja se é isso.

basta adaptar a medida abaixo para o seu contexto.

Acumulado Ano =

CALCULATE(

    [Gross Sales],

    WINDOW(

        1,ABS,

        0,REL,

        ALLSELECTED(dCalendar[Year]),

        ORDERBY(dCalendar[Year],ASC)

    )

)

[Henrique Merola Faria]

46 minutos atrás, Luiz_Mattos disse:

Primeiramente, obrigado pela ajuda.

Usando sua formula, deu o seguinte resultado :

O que eu preciso é, seria que em 2022, fosse a soma do ano anterior + o ano atual ($14.068.829), e a mesma logica para 2023, 2024 etc igual a tabela abaixo:

 

 

Estranho, vc pode colocar aqui a medida [Ganho total recorrente USD MRF atual] por favor ?

 

[Luiz_Mattos]

7 minutos atrás, Henrique Merola Faria disse:

Estranho, vc pode colocar aqui a medida [Ganho total recorrente USD MRF atual] por favor ?

 

Ganho total recorrente USD MRF atual =

[Ganho ano de entrega USD MRF atual] + [Ganho ano seguinte USD MRF atual]
 

Ganho ano de entrega USD MRF atual =

sum(F_CasuloOneDrive_MRF_Atual[Ganho no Ano de Entrega]) / [Cambio MRF atual]
 

Ganho ano seguinte USD MRF atual =

sum(F_CasuloOneDrive_MRF_Atual[Ganho no Ano Seguinte]) / [Cambio MRF atual]

 

[Luiz_Mattos]

36 minutos atrás, Edson Igari disse:

Veja se é isso.

basta adaptar a medida abaixo para o seu contexto.

Acumulado Ano =

CALCULATE(

    [Gross Sales],

    WINDOW(

        1,ABS,

        0,REL,

        ALLSELECTED(dCalendar[Year]),

        ORDERBY(dCalendar[Year],ASC)

    )

)

Também não funcionou, 
 

Teste acumulado 2 =

CALCULATE(

    [Ganho total recorrente USD MRF atual],

    WINDOW(

        1,ABS,

        0,REL,

        ALLSELECTED(CalendarioDax[Ano]),

        ORDERBY(CalendarioDax[Ano],ASC)

    )

)

[Edson Igari]

11 minutos atrás, Luiz_Mattos disse:

Também não funcionou, 
 

Teste acumulado 2 =

CALCULATE(

    [Ganho total recorrente USD MRF atual],

    WINDOW(

        1,ABS,

        0,REL,

        ALLSELECTED(CalendarioDax[Ano]),

        ORDERBY(CalendarioDax[Ano],ASC)

    )

)

Pq vc soma o atual + o seguinte???

[Henrique Merola Faria]

27 minutos atrás, Luiz_Mattos disse:

Ganho total recorrente USD MRF atual =

[Ganho ano de entrega USD MRF atual] + [Ganho ano seguinte USD MRF atual]
 

Ganho ano de entrega USD MRF atual =

sum(F_CasuloOneDrive_MRF_Atual[Ganho no Ano de Entrega]) / [Cambio MRF atual]
 

Ganho ano seguinte USD MRF atual =

sum(F_CasuloOneDrive_MRF_Atual[Ganho no Ano Seguinte]) / [Cambio MRF atual]

 

Bem mais complexo do que eu imaginei.
Alguma possibilidade de vc disponibilizar o pbix com uma amostra dos dados ?

[Luiz_Mattos]

8 minutos atrás, Edson Igari disse:

Pq vc soma o atual + o seguinte???

Tenho que somar as duas medidas para obter o ganho total do projeto. 

Tem projetos que são entregues no mesmo ano e outros no ano atual e no ano seguinte. Ai para ter o ganho total do projeto temos que somar as duas informações.

[Luiz_Mattos]

10 minutos atrás, Henrique Merola Faria disse:

Bem mais complexo do que eu imaginei.
Alguma possibilidade de vc disponibilizar o pbix com uma amostra dos dados ?

Infelizmente não posso disponibilizar com conta da confidencialidade das informações.

Eu estou tentando aqui de diversas formas e não estou conseguindo, até tive que partir pro excel para fazer um grafico pro meu chefe. kkkk

[Edson Igari]

1 minuto atrás, Luiz_Mattos disse:

Tenho que somar as duas medidas para obter o ganho total do projeto. 

Tem projetos que são entregues no mesmo ano e outros no ano atual e no ano seguinte. Ai para ter o ganho total do projeto temos que somar as duas informações.

então coloca essa soma dentro do acumulado pra ver

[Luiz_Mattos]

12 minutos atrás, Henrique Merola Faria disse:

Bem mais complexo do que eu imaginei.
Alguma possibilidade de vc disponibilizar o pbix com uma amostra dos dados ?

Usando esta formula aqui, chego no total de ganho ao longo de 4 anos. Mas não aparece os outros anos nem soma o ano anterior acumulado.

Total de Ganho total recorrente acumulado MRF atual para Date =

SUMX(

    VALUES(CalendarioDax[Ano]),

    CALCULATE(

    [Ganho total recorrente acumulado MRF atual],

    ALLSELECTED('CalendarioDax'[Date])

))
 

 

[Edson Igari]

consegue compartilhar o pbix ou um de exemplo???

[Yuri Ximenes De Figueiredo Mota]

Ola,

Existe relacionamento entra a tabela calendario e a tabela que a medida [Ganho total recorrente USD MRF atual]  se refere? se sim, acredito que essa solução deva funcionar:

Valor Acumulado = 
CALCULATE(
     [Ganho total recorrente USD MRF atual],
     FILTER(ALL(CalendarioDAX),
            Year(CalendarioDax[Date]) < = YEAR(MAX(CalendarioDax[Date])) 
         )
)
 ou

Valor Acumulado = 
CALCULATE(
     [Ganho total recorrente USD MRF atual],
     FILTER(ALL(CalendarioDAX),
            Year(CalendarioDax[Date]) <= YEAR([Data minima])
         )
)

[Luiz_Mattos]

11 minutos atrás, Yuri Ximenes De Figueiredo Mota disse:

Ola,

Existe relacionamento entra a tabela calendario e a tabela que a medida [Ganho total recorrente USD MRF atual]  se refere? se sim, acredito que essa solução deva funcionar:

Valor Acumulado = 
CALCULATE(
     [Ganho total recorrente USD MRF atual],
     FILTER(ALL(CalendarioDAX),
            Year(CalendarioDax[Date]) < = YEAR(MAX(CalendarioDax[Date])) 
         )
)
 ou

Valor Acumulado = 
CALCULATE(
     [Ganho total recorrente USD MRF atual],
     FILTER(ALL(CalendarioDAX),
            Year(CalendarioDax[Date]) <= YEAR([Data minima])
         )
)

A questão é que para o ano de 2022, 2023 e 2024 o valor da tabela original é 0.

O que quero fazer é além de replicar o valor anterior,  quero somar o valor do ano anterior. 

Como esta:

 

Como gostaria:

[Edson Igari]

@Luiz_Mattos, segue:

Luiz_Mattos.pbix

[Henrique Merola Faria]

@Luiz_Mattos, fiz um modelo bem simplificado com as medidas que vc passou,
considerando que os valores vem de colunas de uma única tabela.
Veja se é algo parecido com isso o resultado que vc precisa, no pbix em anexo.

Acumulado Ganhos.pbix

[Henrique Merola Faria]

kkkk....no mesmo instante...

[Luiz_Mattos]

9 minutos atrás, Henrique Merola Faria disse:

@Luiz_Mattos, fiz um modelo bem simplificado com as medidas que vc passou,
considerando que os valores vem de colunas de uma única tabela.
Veja se é algo parecido com isso o resultado que vc precisa, no pbix em anexo.

Acumulado Ganhos.pbix 44.39 kB · 0 downloads

Muito legal ver a galera toda tentando ajudar!

O problema é o seguinte, a tabela de dados dos projetos nesse caso só tem dados em 2021...eu quero que replique 2021 e faça a soma do valor replicado em 2022, igual no exemplo abaixo:

[Henrique Merola Faria]

Ok, e quando tiver dados em 2022 ou 2023, o  que é para acontecer ?
Ou essa é uma situação específica, que não vai mais se repetir ?

[Luiz_Mattos]

1 minuto atrás, Henrique Merola Faria disse:

Ok, e quando tiver dados em 2022 ou 2023, o  que é para acontecer ?
Ou essa é uma situação específica, que não vai mais se repetir ?

Todos os projetos só terão ganhos no ano de sua implantação, nesse exemplo ai foi 2021.

Como são "ganhos recorrentes" em teoria um mesmo ganho de 2021 vai ser replicado para os anos posteriores.  

[Henrique Merola Faria]

@Luiz_Mattos
Certo, que tenho numa outra situação aqui, que posso adaptar para vc:
- Até setembro de 2024, vem acumulando a soma das últimas colunas, fecha em 95k.
- A partir de outubro, não tem mais valor, então acumula o último valor, 10 k, mês a mês.
> No seu caso teria uma linha inicial com os ganhos do ano de implantação e a partir daí
   acumularia ao infinito e além os ganhos recorrentes.

Editado Setembro 11 por Henrique Merola Faria

[Luiz_Mattos]

16 horas atrás, Henrique Merola Faria disse:

@Luiz_Mattos
Certo, que tenho numa outra situação aqui, que posso adaptar para vc:
- Até setembro de 2024, vem acumulando a soma das últimas colunas, fecha em 95k.
- A partir de outubro, não tem mais valor, então acumula o último valor, 10 k, mês a mês.
> No seu caso teria uma linha inicial com os ganhos do ano de implantação e a partir daí
   acumularia ao infinito e além os ganhos recorrentes.

A coluna do total acumulado é uma medida?

[Luiz_Mattos]

Consegui aqui pessoal! Ufa!

Segui esta linha aqui:

1º criei usei esta formula:
 

Total de Ganho total recorrente acumulado MRF atual para Date =

SUMX(

    VALUES(CalendarioDax[Ano]),

    CALCULATE(

    [Ganho total recorrente acumulado MRF atual],

    ALLSELECTED('CalendarioDax'[Date])

))

2º Depois usei a formula anterior nesta outra formula:

Total acumulado por projeto desde de o inicio =

CALCULATE(

    [Total de Ganho total recorrente acumulado MRF atual para Date],

    FILTER(

        ALLSELECTED('CalendarioDax'[Date]),

        ISONORAFTER('CalendarioDax'[Date], MAX('CalendarioDax'[Date]), DESC)

    )

)

Ai funcionou!!



Obrigado o apoio de todos!

[Henrique Merola Faria]

1 hora atrás, Luiz_Mattos disse:

A coluna do total acumulado é uma medida?

Só para não deixar em branco, é uma medida sim.