How to get the difference between 2 rows in a column for category

[Newbie992]

I want to get the difference between the successive rows in a column(Confirmed_cases). I have indexed the columns by category but can't go further. Please help.

[parry2k]

@Newbie992 try this measure

Diff =
VAR __index = SELECTEDVALUE ( Table[Index ) 
VAR __prevIndex = __index - 1
VAR __country = SELECTEDVALUE Table[Country] )
VAR __prevValue = CALULATE ( SUM ( Table[Cases] ), Table[Index] = __prevIndex, Table[Country] = __country )
RETURN
SUM ( Table[Cases] ) - __prevValue

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Newbie992]

Hi,

Thanks for your reply!

It's not working for me. In the result, I get the same values that are in Confirmed_cases column. I checked and in the variable _prevValue I am not getting any values.

Thanks!

[parry2k]

@Newbie992 do you getting value in the _index variable. Can you paste data in a table format so that I can copy it? I probably missed something, I pasted the expression from my phone and was a bit hard to type.

[Newbie992]

Hi @parry2k ,

Yes I see data in __index variable but not in __prevValue.

Here  is a sample of my data:

Location_Key	New_Cases_Date	Confirmed_cases	Index
Afghanistan	2020-04-19 00:00:00	996	1
Afghanistan	2020-04-20 00:00:00	1026	2
Afghanistan	2020-04-21 00:00:00	1092	3
Afghanistan	2020-04-22 00:00:00	1176	4
Afghanistan	2020-04-23 00:00:00	1279	5
Afghanistan	2020-04-24 00:00:00	1351	6
Albania	2020-03-09 00:00:00	2	1
Albania	2020-03-10 00:00:00	10	2
Albania	2020-03-11 00:00:00	12	3
Albania	2020-03-12 00:00:00	23	4

Thanks!

[parry2k · Accepted solution]

@Newbie992 try this

Diff = 
VAR __index = SELECTEDVALUE ( 'Table'[Index] ) 
VAR __prevIndex = __index - 1
VAR __prevValue = 
CALCULATE ( 
    SUM ( 'Table'[Confirmed_cases] ), 
    ALLEXCEPT ( 'Table', 'Table'[Location_Key] ), 
    'Table'[Index] = __prevIndex 
) 
RETURN
SUM ( 'Table'[Confirmed_cases] ) - __prevValue

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Newbie992]

Hi @parry2k ,

It works!

Thank you so much for your help!

Appreciate it!

Kind Regards!