Problem finding single day value

[Mugizib]

Hi everyone, I'm having a problem that should be very simple to solve but I've looked everywhere and couldn't find an answer.

I have a table, like the image below:

In this table I have a name of an individual [name], a date [date] and a running value of this persons total xp [xp].

What I've been strugling is to find the value for each person of each single day. For example, for the character Ulvis, the xp today should be (Ulvis [xp] today) - (Ulvis [xp] yesterday) and so on.

I managed to create a column where it sort of works, but not completely, because the xp can increase and also decrease, and this formula doesn't work if the xp decreases from one day to the other:

XP Diaria =
VAR _maxvalue =
         CALCULATE (
                MAX ( 'XP por dia'[xp] ),
                FILTER (
                         'XP por dia',
                         'XP por dia'[name] = EARLIER ( 'XP por dia'[name] )
                         && 'XP por dia'[Índice] < EARLIER ( 'XP por dia'[Índice] )
                )
         ) 

RETURN
          'XP por dia'[xp] - _maxvalue

Here is my result: 

name	vocation	level	xp	date	Índice	XP Diaria
Best Kina	Elder Druid	435	1.358.049.628	21/01/2023	671	1.358.049.628
Best Kina	Elder Druid	435	1.353.809.182	22/01/2023	1621	-4.240.446
Best Kina	Elder Druid	435	1.353.809.182	23/01/2023	2571	-4.240.446
Best Kina	Elder Druid	435	1.354.952.480	24/01/2023	3524	-3.097.148
Best Kina	Elder Druid	434	1.349.341.097	25/01/2023	4477	-8.708.531
Best Kina	Elder Druid	435	1.356.881.477	26/01/2023	5423	-1.168.151
Best Kina	Elder Druid	436	1.366.596.988	27/01/2023	6369	8.547.360
Best Kina	Elder Druid	436	1.366.596.988	28/01/2023	7322	0
Ulvis	Elder Druid	542	2.629.227.131	21/01/2023	413	2.629.227.131
Ulvis	Elder Druid	543	2.639.848.792	22/01/2023	1364	10.621.661
Ulvis	Elder Druid	544	2.656.424.542	23/01/2023	2310	16.575.750
Ulvis	Elder Druid	545	2.670.269.464	24/01/2023	3261	13.844.922
Ulvis	Elder Druid	546	2.684.681.945	25/01/2023	4209	14.412.481
Ulvis	Elder Druid	547	2.702.924.968	26/01/2023	5154	18.243.023
Ulvis	Elder Druid	548	2.718.618.346	27/01/2023	6105	15.693.378
Ulvis	Elder Druid	549	2.734.267.729	28/01/2023	7053	15.649.383

And here is the expected result:

name	vocation	level	xp	date	Índice	XP Diaria
Best Kina	Elder Druid	435	1.358.049.628	21/01/2023	671	1.358.049.628
Best Kina	Elder Druid	435	1.353.809.182	22/01/2023	1621	-4.240.446
Best Kina	Elder Druid	435	1.353.809.182	23/01/2023	2571	0
Best Kina	Elder Druid	435	1.354.952.480	24/01/2023	3524	1.143.298
Best Kina	Elder Druid	434	1.349.341.097	25/01/2023	4477	-5.611.383
Best Kina	Elder Druid	435	1.356.881.477	26/01/2023	5423	7.540.380
Best Kina	Elder Druid	436	1.366.596.988	27/01/2023	6369	9.715.511
Best Kina	Elder Druid	436	1.366.596.988	28/01/2023	7322	0
Ulvis	Elder Druid	542	2.629.227.131	21/01/2023	413	2.629.227.131
Ulvis	Elder Druid	543	2.639.848.792	22/01/2023	1364	10.621.661
Ulvis	Elder Druid	544	2.656.424.542	23/01/2023	2310	16.575.750
Ulvis	Elder Druid	545	2.670.269.464	24/01/2023	3261	13.844.922
Ulvis	Elder Druid	546	2.684.681.945	25/01/2023	4209	14.412.481
Ulvis	Elder Druid	547	2.702.924.968	26/01/2023	5154	18.243.023
Ulvis	Elder Druid	548	2.718.618.346	27/01/2023	6105	15.693.378
Ulvis	Elder Druid	549	2.734.267.729	28/01/2023	7053	15.649.383

Thanks in advance!

Best regards!

[v-jianboli-msft · Accepted solution]

Hi @Mugizib ,

Please try:

Column = 
var _index = MAXX(FILTER('XP por dia',[name]=EARLIER('XP por dia'[name])&&[Índice]<EARLIER('XP por dia'[Índice])),[Índice])
VAR _maxvalue =
         CALCULATE (
                MAX ( 'XP por dia'[xp] ),
                FILTER (
                         'XP por dia',
                         'XP por dia'[name] = EARLIER ( 'XP por dia'[name] )
                         && 'XP por dia'[Índice] = _index
                )
         ) 
 
RETURN
[xp]-_maxvalue

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Mugizib Mean Time Between Failure (MTBF) - Microsoft Power BI Community