cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mbrough Member
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

 

Mikestock movements.PNG

11 REPLIES 11
Super User
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
Member

Re: Running total subtracting historical measures

mbrough Member
Member

Re: Running total subtracting historical measures

Hi,

 

See above - thanks

Highlighted
Super User
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
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
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
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
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
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