cancel
Showing results for
Did you mean:
mbrough Member

## Running total subtracting historical measures

Hi all,

I have a table of stock movements by date. The latest stock movement is the closing balance. So in the below example, the closing balance was 159.1m. I want to calculate the historical stock balances by subtracting the previous days stock movements.

So the stock balance for 8th Feb would be 158.1m, then 156.1m for 7th Feb and so on.

How could I achieve this? I can calculate a running total which looks backwards but this adds the stock movements and does not subtract.

Thanks

Mike

Mike 11 REPLIES 11 Super User

## Re: Running total subtracting historical measures

Hi @mbrough

can you post a dataset which can be copy pasted?

thx

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut! mbrough Member

## Re: Running total subtracting historical measures

mbrough Member

## Re: Running total subtracting historical measures

Hi,

See above - thanks

Highlighted Super User

## Re: Running total subtracting historical measures

@mbrough

try this calculated column:

```Column =
VAR MaxDte = MAX( data[Date] )
VAR ValuesToSubtract =
CALCULATE(
SUM( data[Net Stock Movement] ),
ALL( data ),
data[Date] < MaxDte,
data[Date] >= EARLIER( data[Date] )
)
RETURN
CALCULATE(
SUM( data[Net Stock Movement] ),
ALL( data ),
data[Date] = MaxDte
) - ValuesToSubtract```

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut! mbrough Member

## Re: Running total subtracting historical measures

Hi thanks. Do you have a solution that is a measure as the table size is in the hundreds of millions and won't handle the calculated column Super User

## Re: Running total subtracting historical measures

@mbrough

a measure can potentially be slower in this case depending on how you will use the results. What is the final goal?

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut! mbrough Member

## Re: Running total subtracting historical measures

Hi,

I'd like to see the stock balances by time period and slice by different dimensions (product category, location etc).

Thanks

Mike Super User

## Re: Running total subtracting historical measures

@mbrough

then it is faster to precumpute the balances within a column, a measure can be slower in this case

if the data comes from a Database you may even knock this out directly in sql

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut! mbrough Member

## Re: Running total subtracting historical measures

Hi. Thanks, the column solution performs well for speed but it doesn't seem to be adding up correctly.

If I use the column in a measure with a MAX or AVG aggregation then it works, but then i can't slice the stock balances by other dimensions (such as product category).

Thanks for your help with this

Mike