Add the difference of the Current row value and Previous row value and restart formula if new State?

[vinceluc22]

State	Date	Cases	Difference
Georgia	4/1/2020	2	2
Georgia	4/2/2020	2	0
Georgia	4/3/2020	3	1
Florida	4/1/2020	5	5
Florida	4/2/2020	3	-2
Florida	4/3/2020	4	1
Texas	4/1/2020	6	6
Texas	4/2/2020	8	2
Texas	4/3/2020	11	3

Above is the result I'm trying to achieve. I'm taking the current row value, subtracting it from the previous row value, and adding the difference in a new Column.

I have a formula that works for a single State below. How can I enable it to restart the formula if it detects a new State? I'm using Power Query editor in Power BI and I sorted all of my States alphabetically and Dates in ascending order.

Differential =

VAR dates = MyTable[Date]

VAR PrevRow =

CALCULATE(

MAX(MyTable[Cases]),

FILTER(MyTable,

MyTable[Date] < dates))

return

MyTable[Cases] - PrevRow

Current, incorrect result:

State	Date	Cases	Difference
Georgia	4/1/2020	2	2
Georgia	4/2/2020	2	0
Georgia	4/3/2020	3	1
Florida	4/1/2020	5	2
Florida	4/2/2020	3	-2
Florida	4/3/2020	4	1
Texas	4/1/2020	6	2
Texas	4/2/2020	8	2
Texas	4/3/2020	11	3

[camargos88]

Hi @vinceluc22 ,

Try this calculated column:

Diff =

VAR _date = 'Table'[Date]

VAR _maxDate = CALCULATE(MAX('Table'[Date]); FILTER(ALL('Table'); 'Table'[State] = EARLIER('Table'[State]) && 'Table'[Date] < _date))

VAR _previousValue = CALCULATE(SUM('Table'[Cases]); FILTER(ALL('Table'); 'Table'[State] = EARLIER('Table'[State]) && 'Table'[Date] = _maxDate))

return 'Table'[Cases] - _previousValue

Ricardo

[amaniramahi · Accepted solution]

Hi,

I am not an expert but what I did gave me the needed result

I created two columns

1. DateKey = CONCATENATE(Sheet1[State],Sheet1[Date].[Date])

2. PreviousKey = CONCATENATE(Sheet1[State],PREVIOUSDAY(Sheet1[Date].[Date]))

this to make sure that we calculate the difference between the currecnt date and the previous date for the same state

Then a third column for the difference

Difference = 
var Check = CONTAINS(Sheet1,Sheet1[DateKey],Sheet1[PreviousKey])
return
IF(Check=FALSE(),Sheet1[Cases],Sheet1[Cases]-LOOKUPVALUE(Sheet1[Cases],Sheet1[DateKey],Sheet1[PreviousKey]))

and the results as  you need

[vinceluc22]

This worked without any issues, thanks!