Running total by date with positive and negative values

[hjrodrig]

Hey Guys,

I'm trying to do a simple running total going by date of a value that includes positive and negative values. I'm using the following calculation:

Cashflow Running Total =

CALCULATE(Cashflow

FILTER( ALLSELECTED('Mock’ [Date]),

'Mock’[Date] <= max('Mock’[Date])))

The issue here is that it does the running totals for positive and negative values separately (See ' Wrong Running Total' column). What I need is the right running total below but can't figure it out. I'm sure its super simple. Any help would be appreciated! Thanks guys.

Date	Cashflow	Wrong Running Total	Right Running Total
1/2/2019	1	1	1
1/3/2019	0	1	1
1/4/2019	5	6	6
1/14/2019	-5	-5	1
3/12/2019	2	8	3
3/13/2019	2	10	5
3/14/2019	-2	-7	3
6/23/2019	1	11	4
6/24/2019	4	15	8
6/25/2019	5	20	13

[Nathaniel_C · Accepted solution]

Hi @hjrodrig ,
Try this:

Calc run total = 
var _endate =LASTDATE(mock[Date])
return CALCULATE([Cashflow Amount],ALLEXCEPT(mock,mock[Date]),mock[Date]<=_endate)

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @hjrodrig ,
Try this:

Calc run total = 
var _endate =LASTDATE(mock[Date])
return CALCULATE([Cashflow Amount],ALLEXCEPT(mock,mock[Date]),mock[Date]<=_endate)

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[mwegener]

Hi @hjrodrig ,

what does your Cashflow measure?

Do you use a date table?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[hjrodrig]

Cashflow is just a net transactional value for that day on the date field. That all the data I have, so probably don't use a date table. Really appreciate it!

[mwegener]

I would always recommend a date table.

And I hope your measure makes a simple sum.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Nathaniel_C]

Hi @hjrodrig , @mwegener 

Cashflow Amount = SUM(mock[Cashflow])

For the previous measure.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[hjrodrig]

Thank you Nathaniel, this is definitely it, but I ran into the following issue. A particular date might have multiple transactions in one day in the real dataset, so I'm thinking this is where the error is coming from.

Thanks a lot man.

[hjrodrig · Accepted solution]

Sorry pic is not going through but I get an error message saying " A single value for column 'Date' in table x cannot be determined. This happens when an aggregation min, max, sum etc is not used...."