Increment of previous date value

[jeyare]

Hi,

can't find a solution for callculation of increments based on few conditions.

Current table example in my Power Query:

date	person	performance
4.4.2020	A	10
4.4.2020	B	12
4.4.2020	C	8
4.4.2020	D	15
5.4.2020	A	12
5.4.2020	B	16
5.4.2020	C	10
5.4.2020	D	5
6.4.2020	A	9
6.4.2020	B	8
6.4.2020	C	11
6.4.2020	D	7
7.4.2020	A	6
7.4.2020	B	4
7.4.2020	C	20
7.4.2020	D	11

Expected solution:

I need calculate the increment of column "performance" based on previous date value include column "person" evaluation:

date = 4.4.2020 for person = A  there is performance = 10

date = 5.4.2020 for person = A  there is performance = 12

then increment is = 2

date	person	performance	Increment
4.4.2020	A	10	0
4.4.2020	B	12	0
4.4.2020	C	8	0
4.4.2020	D	15	0
5.4.2020	A	12	2
5.4.2020	B	16	4
5.4.2020	C	10	2
5.4.2020	D	5	-10
6.4.2020	A	9	-3
6.4.2020	B	8	-8
6.4.2020	C	11	1
6.4.2020	D	7	2
7.4.2020	A	6	-3
7.4.2020	B	4	-4
7.4.2020	C	20	9
7.4.2020	D	11	4

Thx for a help.

J.

[amitchandak]

@jeyare ,

Try like

diff =
var _last = maxx(filter(Table,table[person]=earlier(table[person]) && table[Date] = earlier(table[Date])-1),table[performance])
return
if(isblank(_last),0,performance-_last)

[Greg_Deckler · Accepted solution]

So basically, as in the article referenced, something like:

Increment Column =
  VAR __Current = [performance]
  VAR __PreviousDate = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] < EARLIER([date])),[date])
  VAR __Previous = MAXX(FILTER('Table',[person] = EARLIER([person]) && [date] = __PreviousDate),[performance])
RETURN
  __Current - __Previous

[jeyare]

thx, seems to be fine until first row, when I get an error:

The value for 'performance' cannot be determined. Either 'performance' doesn't exist, or there is no current row for a column named 'Tperformance'.

Then IF needs to be there, but not sure where

[jeyare]

my reply was for @Greg_Deckler 

[Greg_Deckler]

Hmm, see attached PBIX. Can't say what went wrong but the PBIX seems to work fine.

[jeyare]

thx @Greg_Deckler , my fault

[Greg_Deckler]

No worries! Glad we got it solved! 🙂

[Greg_Deckler]

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...