How can I calculate an increment between two accumulated values?

[Marketeasing]

 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:00	0	0
Afghanistan	21/02/2020 0:00	0	0
Afghanistan	22/02/2020 0:00	0	0
Afghanistan	23/02/2020 0:00	0	0
Afghanistan	24/02/2020 0:00	1	1
Afghanistan	25/02/2020 0:00	1	0
Afghanistan	26/02/2020 0:00	1	0
Afghanistan	27/02/2020 0:00	1	0
Afghanistan	28/02/2020 0:00	4	3
Afghanistan	29/02/2020 0:00	4	0
Afghanistan	01/03/2020 0:00	5	1
Afghanistan	02/03/2020 0:00	7	2
Afghanistan	03/03/2020 0:00	7	0
Afghanistan	04/03/2020 0:00	7	0
Afghanistan	05/03/2020 0:00	11	4
Afghanistan	06/03/2020 0:00	16	5
Afghanistan	07/03/2020 0:00	21	5
Afghanistan	08/03/2020 0:00	22	1
Afghanistan	09/03/2020 0:00	22	0
Afghanistan	10/03/2020 0:00	22	0
Afghanistan	11/03/2020 0:00	24	2
Afghanistan	12/03/2020 0:00	24	0
Afghanistan	13/03/2020 0:00	40	16
Afghanistan	14/03/2020 0:00	40	0

[Vvelarde · Accepted solution]

@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

[Vvelarde · Accepted solution]

@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

[Anonymous]

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.

[Marketeasing]

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]

@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

[Marketeasing]

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 · Accepted solution]

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