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.
Solved! Go to Solution.
Generally recursive calculations can be replicated non-recursively using a combination of cumulative measures, as measures and calculated columns can't self-reference.
See sample model here:
https://www.dropbox.com/s/9zqbq4gvhsg9bba/Circular%20reference%20calculation.pbix?dl=0
This is the output:
To produce this, let's suppose these are your two source tables:
'Initial Stock' table
Data table
Then this series of measures will produce your closing and opening stock by period:
Initial Stock Measure = SUM ( 'Initial Stock'[Intial Stock] ) // This measure should be constant over time and represents Stock at time zero Production Sum = SUM ( Data[Production] ) Market Sum = SUM ( Data[Market] ) Production Cumulative = CALCULATE ( [Production Sum], FILTER ( ALL ( Data[Time index] ), Data[Time index] <= MAX ( Data[Time index] ) ) ) Market Cumulative = CALCULATE ( [Market Sum], FILTER ( ALL ( Data[Time index] ), Data[Time index] <= MAX ( Data[Time index] ) ) ) Shortfall Cumulative = [Market Cumulative] - ( [Initial Stock Measure] + [Production Cumulative] ) Shortfall Cumulative Maximum So Far = MAX ( MAXX ( FILTER ( ALL ( Data[Time index] ), Data[Time index] <= MAX ( Data[Time index] ) ), [Shortfall Cumulative] ), 0 ) Closing Stock = [Initial Stock Measure] + [Production Cumulative] - [Market Cumulative] + [Shortfall Cumulative Maximum So Far] Opening Stock = CALCULATE ( [Closing Stock], FILTER ( ALL ( Data[Time index] ), Data[Time index] = MIN ( Data[Time index] ) - 1 ) )
Anyway, this is just an example to illustrate it can be done 🙂
Generally recursive calculations can be replicated non-recursively using a combination of cumulative measures, as measures and calculated columns can't self-reference.
See sample model here:
https://www.dropbox.com/s/9zqbq4gvhsg9bba/Circular%20reference%20calculation.pbix?dl=0
This is the output:
To produce this, let's suppose these are your two source tables:
'Initial Stock' table
Data table
Then this series of measures will produce your closing and opening stock by period:
Initial Stock Measure = SUM ( 'Initial Stock'[Intial Stock] ) // This measure should be constant over time and represents Stock at time zero Production Sum = SUM ( Data[Production] ) Market Sum = SUM ( Data[Market] ) Production Cumulative = CALCULATE ( [Production Sum], FILTER ( ALL ( Data[Time index] ), Data[Time index] <= MAX ( Data[Time index] ) ) ) Market Cumulative = CALCULATE ( [Market Sum], FILTER ( ALL ( Data[Time index] ), Data[Time index] <= MAX ( Data[Time index] ) ) ) Shortfall Cumulative = [Market Cumulative] - ( [Initial Stock Measure] + [Production Cumulative] ) Shortfall Cumulative Maximum So Far = MAX ( MAXX ( FILTER ( ALL ( Data[Time index] ), Data[Time index] <= MAX ( Data[Time index] ) ), [Shortfall Cumulative] ), 0 ) Closing Stock = [Initial Stock Measure] + [Production Cumulative] - [Market Cumulative] + [Shortfall Cumulative Maximum So Far] Opening Stock = CALCULATE ( [Closing Stock], FILTER ( ALL ( Data[Time index] ), Data[Time index] = MIN ( Data[Time index] ) - 1 ) )
Anyway, this is just an example to illustrate it can be done 🙂
Awesome approach !!
Great approach. However, I see that the "back order" are shortshipped, therefore some orders (market) are lost and we have 65 units at the end. What do we need to change in the formulas to allow stock to be shipped at a later date when available ? (therefore end with a zero closing stock). Tks
You should not write these as columns - use measures instead. Do you have some way to identify the order of the rows? E.g. A date? You will need this - power pivot is a database, not a spreadsheet and hence there's is no Implied order of the rows.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |