Value difference between rows grouped by value

[adeptus2008]

Hi there, 

I have a table like that:

account_id	orders_id	cdate	expdate	money
218966	1073732	22.05.2018	22.05.2019	5
281343	1099694	30.11.2018	30.11.2019	15
326276	1125032	30.01.2019	23.01.2020	8
218966	1285798	08.01.2020	08.01.2021	35
326276	1293403	23.01.2020	23.01.2021	4
281343	1296352	29.01.2020	12.04.2021	9
218966	1297583	30.01.2020	30.02.2020	10

I want to calculate money difference, between curent and previous rows within the group of unique account_id. Thanks for any ideas.

My desrired table with calculated column would be

account_id	orders_id	cdate	expdate	money	money_diff
218966	1073732	22.05.2018	22.05.2019	5	0
281343	1099694	30.11.2018	30.11.2019	15	0
326276	1125032	30.01.2019	23.01.2020	7	0
218966	1285798	08.01.2020	08.01.2021	35	30
326276	1293403	23.01.2020	23.01.2021	4	-3
281343	1296352	29.01.2020	12.04.2021	9	-6
218966	1297583	30.01.2020	30.02.2020	10	-25

[v-yiruan-msft]

Hi @adeptus2008,

Whether you got your expected result by creating any measure or calculated column? If still not, you can try to create one calculated column as below:

money_diff =

if(COUNTROWS(filter('ATable','ATable'[account_id]=EARLIER('ATable'[account_id])&&'ATable'[orders_id]<EARLIER('ATable'[orders_id])))=0,

0,

'ATable'[money]- CALCULATE(max('ATable'[money]),filter('ATable','ATable'[account_id]=EARLIER('ATable'[account_id])&&'ATable'[orders_id]<EARLIER('ATable'[orders_id]))))

Best Regards

Rena

[amitchandak · Accepted solution]

Create new columns like

 new column last date= maxx(filter(table1,table1[account_id]=earlier(account_id) && table1[expdate]<earlier(table1[expdate]))table1[expdate]) 
 new column money_diff = table[money] -maxx(filter(table1,table1[account_id]=earlier(account_id) && table1[expdate]=earlier(table1[new column last date]))table1[money]) 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

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