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

  • Marketeasing's avatar
    Marketeasing
    Frequent Visitor
    6 years ago

    Hello Victor, I've tried the expression that you said but I have big problems with the performance when is loading data.

    This solition is non-viable.

     

    Do you know how to create the same column from DAX? because woth powerquery doesn't work. 

     

    I don't know why bit to access the rows through the index position is very slow.

     

    Thank toy in advance

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

      Marketeasing 

       

      Try with this Dax Calculated Column:

       

       

      Mycolumn =
'Table'[CasesAcum]
    - CALCULATE (
        FIRSTNONBLANK ( 'Table'[CasesAcum]; 'Table'[CasesAcum] );
        TOPN (
            1;
            FILTER (
                'Table';
                'Table'[Name] = EARLIER ( 'Table'[Name] )
                    && 'Table'[Date] < EARLIER ( 'Table'[Date] )
            );
            'Table'[Date]; DESC
        )
    )

       

      Or

       

      Mycolumn =
'Table'[CasesAcum]
    - CALCULATE (
        LASTNONBLANK ( 'Table'[CasesAcum]; 'Table'[CasesAcum] );
        FILTER (
            'Table';
            'Table'[Name] = EARLIER ( 'Table'[Name] )
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )

       

       

      Regards

       

      Victor

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Vvelarde,

     

    Thanks for your answer.

     

    However, If I run the code as you wrote, all the results will shift up for 1 row. We can see that the first row has value, while the last row is null. The target answer should be an empty value in the first row with a value in the last row.

     

    But I think the logic is perfect here, I don't what costs this problem.