Forum Discussion

Marketeasing's avatar
Marketeasing
Frequent Visitor
6 years ago
Solved

How can I calculate an increment between two accumulated values?

 Hello, I have to calculate a new column CasesDay (red color) as the variation of the accumulated column CasesAcum Thanks in advance Name Date CasesAcum CasesDay Afghanistan 20/02/2020 0:...
Need Help
  • Vvelarde's avatar
    Vvelarde
    6 years ago

    Marketeasing 

     

    Hi, Try this:

     

    1. Add a Index Column

     

    2. Add a Custom Column

    try #"Added Index"{[Index]}[CasesAcum]-#"Added Index" {[Index]-1}[CasesAcum] otherwise 0

     

    Regards

     

    Victor

     

  • Marketeasing's avatar
    Marketeasing
    6 years ago

    Finally I've created a new formula to detect the change of name and that's solved

     

    = Table.AddColumn(#"Índice agregado", "Cases", each if #"Índice agregado"{[Índice]}[#"Name"] = #"Índice agregado"{[Índice]-1}[#"Name"] then #"Índice agregado"{[Índice]}[#"CasesAcum"]-#"Índice agregado"{[Índice]-1}[#"CasesAcum"] else 0)

     

    Thank you Victor

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
6 years ago

Marketeasing 

 

Hi, Try this:

 

1. Add a Index Column

 

2. Add a Custom Column

try #"Added Index"{[Index]}[CasesAcum]-#"Added Index" {[Index]-1}[CasesAcum] otherwise 0

 

Regards

 

Victor

 

Marketeasing's avatar
Marketeasing
Frequent Visitor
6 years ago

Hello victor, thank you for your answer, but there are more than one value in the column "Name", and I need to create an index for each "Name" value. Do you know how to do that? Thank you again 

  • Marketeasing's avatar
    Marketeasing
    Frequent Visitor
    6 years ago

    Finally I've created a new formula to detect the change of name and that's solved

     

    = Table.AddColumn(#"Índice agregado", "Cases", each if #"Índice agregado"{[Índice]}[#"Name"] = #"Índice agregado"{[Índice]-1}[#"Name"] then #"Índice agregado"{[Índice]}[#"CasesAcum"]-#"Índice agregado"{[Índice]-1}[#"CasesAcum"] else 0)

     

    Thank you Victor