Previous value with group/category

[sonm10]

Hello all,

I have values for 3 items against date. I want to calculate the rate of change of the value for the item.The dates are not necessarily consecutives and each item may not have the same dates. If there was no item, I can use index to track the previous value. However with items I am lost.  I want my data as follows.

Date	Item	Value	Return (prev value - current)
11/12/2020	A	1
11/15/2020	A	2	1
11/16/2020	A	3	0.5
11/17/2020	A	2	-0.333333333
11/18/2020	A	1	-0.5
11/19/2020	A	2	1
11/21/2020	A	1	-0.5
11/22/2020	A	4	3
11/26/2020	A	5	0.25
11/12/2020	B	12
11/15/2020	B	11	-0.083333333
11/16/2020	B	12	0.090909091
11/17/2020	B	3	-0.75
11/18/2020	B	21	6
11/19/2020	B	1	-0.952380952
11/21/2020	B	2	1
11/22/2020	B	4	1
11/26/2020	B	6	0.5
11/12/2020	C	7
11/15/2020	C	1	-0.857142857
11/16/2020	C	2	1
11/17/2020	C	3	0.5
11/18/2020	C	5	0.666666667
11/19/2020	C	6	0.2
11/21/2020	C	7	0.166666667
11/22/2020	C	8	0.142857143
11/26/2020	C	6	-0.25

Thank you.

[mahoneypat]

Please try this column expression that gets your desired results.

Change =
VAR thisvalue = 'Table'[Value]
VAR thisitem = 'Table'[Item]
VAR thisdate = 'Table'[Date]
VAR prev =
CALCULATE (
LASTNONBLANKVALUE ( 'Table'[Date], MAX ( 'Table'[Value] ) ),
ALL ( 'Table' ),
'Table'[Date] < thisdate,
'Table'[Item] = thisitem
)
RETURN
DIVIDE ( thisvalue - prev, prev )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@sonm10 , Try a new column like

New column =
Var _Last_date = maxx(filter(table,[Date]<earlier([Date])),[Date])
var _Last_value = maxx(filter(table,[Date]=_Last_date),[Value])
return 
divide([Value]-_Last_value,_Last_value)

[sonm10]

Thank you @amitchandak for your solution. However, since I have multiple items on the same date and i want the calculation to be done for each separate item, its not working. Your solution is working if there is no multiple item

[Greg_Deckler]

@sonm10 - If I understand what you are asking, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date]) && [Item] = EARLIER([Item])),[Value])
RETURN
  __Current - __Previous

[sonm10]

Hello @Greg_Deckler. I have implemented your solution but few data are coming right but most of them are incorrect. Attached below the screenshot of the sample dataset.

I have calcualted the new column as follows:

Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Test','Test'[Date] < EARLIER('Test'[Date]) && [Group] = EARLIER([Group])),[Value])
RETURN
__Current - __Previous

The return field is what should be the correct value. Its been calculated in excel for comparisons.

[Greg_Deckler · Accepted solution]

@sonm10 - Sorry, I messed that up. 

Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Test','Test'[Date] < EARLIER('Test'[Date]) && [Group] = EARLIER([Group])),[Date])
VAR __Previous = MAXX(FILTER('Test','Test'[Date] = __PreviousDate && [Group] = EARLIER([Group])),[Value])
RETURN
__Current - __Previous