How to calculate a simple running total?

[webportal]

This is table: Customer

And this is table Transactions:

They are related by No.

I want the running total of Esaldo:

So:

Running total =

CALCULATE(

SUM(Transactions[ESaldo]),

FILTER(

ALL(

Transactions),

Transactions[Date] <= MAX(Transactions[Date])

)

)

This is wrong:

Due to the external filter (slicer).

So, this should do it:

Running total 2 =

CALCULATE(

SUM(Transactions[ESaldo]),

FILTER(

ALLEXCEPT(Transactions, Transactions[no]),

Transactions[Date] <= MAX(Transactions[Date])

)

)

If the ALLEXCEPT was meant to be understood... It should remove all the filters from Transactions, and preserve the filter in column No.

But it wasn't and thus the result is the same:

So, maybe... ALLSELECTED?

Running total 3 =

CALCULATE(

SUM(Transactions[ESaldo]),

ALLSELECTED(Transactions[no])

)

Nope:

[webportal]

@camargos88 

I did, and it worked!

In only 3 hours of work I have a running total!

😀

[camargos88 · Accepted solution]

@webportal ,

Have you checked the file? 

[webportal]

@camargos88 

[camargos88]

@webportal ,

Check the attached file, you need to keep the filter on name and ignore the other columns.

[webportal]

And how about...

CALCULATE(

SUM(Transactions[ESaldo]),

FILTER(

ALL(Transactions),

Transactions[Date] <= MAX(Transactions[Date]) && Transactions[no] = SELECTEDVALUE(Customer[no])

)

)

[camargos88]

@webportal ,

[camargos88]

@webportal ,

As I can see it's correct.

the last line is the same as the result. no ?

[webportal]

@camargos88 

It is still wrong, because if you change the slicer to DEF, you get:

[webportal]

@camargos88 

It is still wrong:

[camargos88]

@webportal ,

Try this measure:

_RT = CALCULATE(SUM(Transactions[ESaldo]), FILTER(ALLEXCEPT('Transactions', Customer[Name]), 'Transactions'[Date] <= MAX('Transactions'[Date])))

[webportal]

As you may see, it is still wrong...

[webportal]

Change the slicer to "DEF"...

[camargos88]

@webportal ,

You need to create a table date, and refer it inside the measure for running total.

[webportal]

@Fowmy 

@camargos88 

You may find the PBIX here:
https://drive.google.com/drive/folders/1W5cnLtIBD9REYbr3VdhIZqTcCtxz62hp?usp=sharing 

[Fowmy]

@webportal 

Your below measure should work, what id you get when you applied it?

Running total 2 =

CALCULATE(

SUM(Transactions[ESaldo]),

FILTER(

ALLEXCEPT(Transactions, Transactions[no]),

Transactions[Date] <= MAX(Transactions[Date])

)

)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[camargos88]

@webportal ,

Your first used ALL(Transactions), this one is with ALL(Transactions[Date]).

Is still wrong using the date inside all ?

[camargos88]

@webportal ,

Try this one:

CALCULATE(
SUM(Transactions[ESaldo]),
FILTER(
ALL(Transactions[Date]),
Transactions[Date] <= MAX(Transactions[Date])
)
)

[webportal]

@camargos88 

I sure tried that one too...

[webportal]

@camargos88 

Thanks a lot, that was exactly my first atempt, as you can read in my post, but it is wrong.