cancel
Showing results for
Did you mean:  Helper I

## Calculate Running Total Using Measures

I'd like to create a column that calculates the running total of a measure. It's a bit more complicated than that, because it uses two measures: Over and Under. If the previous Over > 0, then add it to the current Over. If the previous Under > 0, then subtract it from the current Over. If that difference is positive, then show that. If it is negative, make the current equal to zero. I don't want the Under amounts to be summed, only the Over.

Here's some sample data:

 Under Over 0 4 0 10 11 0 14 0 0 4 0 4 0 4

I want the running total column to be:

RunningTotal
4 = 4 from Over
14 = 4 + 10 from Over
3 = 14 - 11 from Under
0 = from Over
4 = from Over
8 = 4 + 4 from Over
12 = 8 + 4 from Over

Table:

 Under Over Running Over Total - Under 0 4 4 0 10 14 11 0 3 14 0 0 0 4 4 0 4 8 0 4 12

Here's what I have so far, but it's not working:

``````RunningTotal =
var filterDate = LASTDATE(Sheet1[Date])
var output = CALCULATE(
IF (
[Under] > 0,
[Over] - [Under],
[Over]
),
ALL(Sheet1),
Sheet1[Date] <= filterDate
)
RETURN IF (
output > 0,
ouput,
0
)``````

It's returning:

 RunningTotal 4 14 3 0 0 0 1

5 REPLIES 5  Community Support

Hi , @Lyssillic

I'm sorry  that I tried for a while but still didn't work out.

The logic is easy to understand but the formula is not easy to write.

In addition, I will try to find someone who is familiar with this to take a further look at this.

I will post back if we have any conclusions on this topic.

Best Regards,
Community Support Team _ Eason  Helper I

@v-easonf-msft Thank you!  Super User

agree with @v-easonf-msft :  the logic is understandable, but unfortunately my DAX skills are not good enough to come up with a solution here. Problem is that you need "real" recurion to set back to 0 with regards to the previous value. Problem with the current formula is that the negative running total caused by the 14 "under" will have to be compensated by the following overs before getting positive again.

With regards on how to convert your existing measure to a calculated column - it would be this:

``````RunningTotal =
var filterDate = LASTDATE(Sheet1[Date])
var output = CALCULATE(
SUMX(Sheet1, IF (
[Under] > 0,
[Over] - [Under],
[Over]
)),
ALL(Sheet1),
Sheet1[Date] <= filterDate
)
RETURN IF (
output > 0,
output,
0
)``````

But it will just return the same value than your current measure in the column.
In Power Query one would use List.Generate or List.Accumulate, but if you run them without a Table.Buffer (as this wouldn't work in Direct Query mode) the result will be very slow for larger datasets.

Posting the PQ sample here in case it helps some DAX genius to pick up the logic:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTKBYgNDXSAyMjC0VIrVgUgZggmwpBGKpKEhSAyIjUG0MaqcCVQOjE0wDIXbZ4pVygIkZYZVytAIJGeOkIsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Under = _t, Over = _t, #"Running Over Total - Under" = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Under", Int64.Type}, {"Over", Int64.Type}, {"Running Over Total - Under", Int64.Type}, {"Date", type date}}),
BufferedTable = Table.Buffer(#"Changed Type"),
RunningTotal = List.Skip(List.Generate( () =>
[RowResult = 0, CumulativeResult = 0, Counter = 0],
each [Counter] <= Table.RowCount(BufferedTable),
each [
CurrentRow = BufferedTable{[Counter]},
RowResult = if CurrentRow[Under] > 0
then CurrentRow[Over] - CurrentRow[Under]
else CurrentRow[Over],
CumulativeResult = List.Max( { [CumulativeResult] + RowResult, 0 } ),
Counter = [Counter] + 1
],
each [CumulativeResult]
)),
Result = Table.FromColumns(  Table.ToColumns( BufferedTable ) & { RunningTotal }, Table.ColumnNames(BufferedTable) & {"RunningTotal"} )
in
Result``````

Actually, even without the buffer the complicated List.Generate-logic wouldn't work in Direct Query unfortunately.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!  Super User

Hi

there is usually no use for measures in calculated columns.

That's because calculated columns are calculated/refreshed when you refresh the data model to catch changes in the underlying data.

Measures on the other hand respond to user interactions with the reports.

So if you want to create a column with DAX it's best practice to do this with column operations only.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!  Helper I

@ImkeF Okay, could you help me translate this into a calculated column? Also, I'm restricted to only use DirectQuery.   