How to create a value from the increment of other values

[Marketeasing]

Hello, I have to calculate a new column CasesDay (red color) as the variation of the accumulated column CasesAcum.

I have more than 1 Name, so I have to calculate the increment for each row of each "Name". The registers are ordered by Name,Date

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

[camargos88 · Accepted solution]

Hi @Marketeasing ,

Try this code for a new calculated column:

CasesDay =

VAR _name = 'Table'[Name]

VAR _date = 'Table'[Date]

VAR _lastDate = CALCULATE(MAX('Table'[Date]); FILTER(ALL('Table'); 'Table'[Name] = _name && 'Table'[Date] < _date))

VAR _lastValue = CALCULATE(SUM('Table'[CasesAcum]); FILTER(ALL('Table'); 'Table'[Name] = _name && 'Table'[Date] = _lastDate))

RETURN 'Table'[CasesAcum] - _lastValue

Ricardo

[Marketeasing]

Thank you @camargos88 . Perfect!!