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, 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