MAXX and MINX calculating wrongly on measure

[jmkvalsund]

I've made a running total measure (Balance) to my table Transactions like this:

Balance = Calculate(SUM(Transactions[AccountValue]),FILTER(ALL(Transactions[TransactionsDTM]),Transactions[TransactionsDTM]<=MAX(Transactions[TransactionsDTM]))) 

This one works, I've  put the same data in Excel and compared cell by cell, Balance is calculated correct.

Then I'try to find the Max- and Min-value of Balance like this:

Balance MAX = MAXX(ALL(Transactions ),Transactions [Balance])

The expression is evaluated and returns data, but the numbers are wrong, in the order of Max supposed to be 19 millions but shows up 21 millions, Min supposed to be -60 millions, shows -90 millions.

Any suggestions of where I'm failing?

Regards,

John Martin

[Anonymous · Accepted solution]

Hi @jmkvalsund ,

Can you provide dummy data and expected results?
In the dummy data I created myself, Balance and AccountValue have the same results.

Are you trying to calculate the cumulative value?

If so, you can try to remove the fields in the ALL function and modify them as follows.

Balance = Calculate(SUM(Transactions[AccountValue]),FILTER(ALL(Transactions),Transactions[TransactionsDTM]<=MAX(Transactions[TransactionsDTM]))) 

Balance MAX = MAXX(ALL(Transactions),[Balance])

Best Regards,

Stephen Tao

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

[jmkvalsund]

Thanks a lot, this works!

[ValtteriN]

Hi,

Try changing your RT measure like this:
Calculate(SUM(Transactions[AccountValue]),ALL(Transactions[TransactionsDTM]),Transactions[TransactionsDTM]<=MAX(Transactions[TransactionsDTM])))

Explanation:

RT calculation works also with the filter, but the interaction with external filters is a bit different:

Period RT FILTER =

VAR MaxDate = MAX ( 'Cumulativetotal'[Date] )

RETURN

CALCULATE (

SUM(Cumulativetotal[Value]),

FILTER(ALLSELECTED('Cumulativetotal') ,'Cumulativetotal'[Date] <= MaxDate))

Period RT =

VAR MaxDate = MAX ( 'Cumulativetotal'[Date] )

RETURN

CALCULATE (

SUM(Cumulativetotal[Value]),

'Cumulativetotal'[Date] <= MaxDate,

ALLSELECTED('Cumulativetotal') )

Now when we introducte a filter this happens:

I suspect this might be the reason your MAXX is not working properly.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[jmkvalsund]

Hi, I tried your suggestion, but the result is still the same.
But then I narrowed down my datasource to contain just a few records, and now I see whats wrong:
The Max- and Min-values are not taken from the RT measure [Balance], but from the values in the column Transactions[AccountValue]...
Why??
The formula doesn't include this column at all, but the RT measure is based upon it. How does MAXX return values from the measure's datasource instead of the measure's values??