cancel
Showing results for
Did you mean:
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

Proud to be a Datanaut!

Member

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```

Proud to be a Datanaut!

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?

Proud to be a Datanaut!

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

Proud to be a Datanaut!

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