cancel
Showing results for
Did you mean:
Regular Visitor

## Problem finding single day value

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

Best regards!

1 ACCEPTED SOLUTION
Community Support

Hi @Mugizib ,

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

2 REPLIES 2
Community Support

Hi @Mugizib ,

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

Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...