Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

How would you like to make stock balances change with other dimensions selected (such as product category)?

You could use "allselected" in your meausre to replace "ALL" function, then when you select different dimensions, it shows different results.

 

If it doesn't solve your probelm, please show more details as LivioLanzo's last post suggested.

 

Best Regards

Maggie

LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous

 

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!  

Anonymous
Not applicable

Anonymous
Not applicable

Hi,

 

See above - thanks

@Anonymous

 

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!  

Anonymous
Not applicable

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

@Anonymous

 

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!  

Anonymous
Not applicable

Hi,

 

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

 

Thanks

 

Mike

@Anonymous

 

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!  

Anonymous
Not applicable

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

Hi @Anonymous

 

it makes sense it does not work for all dimensions because balances are a semi-additive snapshot. Can you show me your dimension plus the other columns within the stock movements table?

 


 


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


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.