Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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!
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!
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!
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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |