calculate credits used based on date and user

[moltrasms]

I am trying to figure out how to calculate the credits used by each user each day.  the source of the credits is a totalizer for each user. I am putting the information in google sheets.  I am trying to calculate automatically the Credits used column in the below table. 

date		time		Email		Credits		Credits used
5/24/2022		1:15:00 PM		user1		18240		Not Calculated
5/24/2022		1:15:00 PM		user2		16112		Not Calculated
5/24/2022		1:15:00 PM		user3		17842		Not Calculated
5/24/2022		1:15:00 PM		user4		48344		Not Calculated
5/25/2022		1:00:00 PM		user1		37445		19205
5/25/2022		1:00:00 PM		user2		57836		41724
5/25/2022		1:00:00 PM		user3		17842		0
5/25/2022		1:00:00 PM		user4		70942		22598
5/25/2022		1:00:00 PM		User5		3566		3566
5/26/2022		1:30:00 PM		user1		42,840		5,395
5/26/2022		1:30:00 PM		user2		107,535		49,699
5/26/2022		1:30:00 PM		user3		21,762		3,920
5/26/2022		1:30:00 PM		user4		86,393		15,451
5/26/2022		1:30:00 PM		User5		4,392		826
5/27/2022		3:30:00 PM		user1		48,235		5,395
5/27/2022		3:30:00 PM		user2		157,234		49,699
5/27/2022		3:30:00 PM		user3		25,681		3,920
5/27/2022		3:30:00 PM		user4		101,844		15,451
5/27/2022		3:30:00 PM		user5		5,218		826

The calculation can be done in Google Sheets, Power Query, or Dax which every is best.

[v-jayw-msft · Accepted solution]

Hi @moltrasms ,

Please check the formula.

Column = 
var next = CALCULATE(SUM('Table'[Credits]),FILTER('Table','Table'[Email]=EARLIER('Table'[Email])&&'Table'[date]=EARLIER('Table'[date])-1))
var used = 'Table'[Credits]-next
return
IF(used='Table'[Credits],"Not Calculated",FORMAT(used,""))

Best Regards,

Jay