recursive calculation

[Alphacom]

Hi, i need help, i'm in thi situation, i have table like this:

date              product        order_qty        forniture_qty      stock_qty   

01/01/2024    bag               10                   5                        5       

02/01/2024    bag               11                   7                        5          

.....

order_qty and forniture_qty is specified date value, stock_qty is the same for all date, is the quantity i have in warehose.  

I need , date by date, to calculet the amont of bag i need (+ or -) respect orderd by customer and order to forniture, but stock _qty i need to decrease dy by day:

01/01/2024  order 10 - forniture 5 - stock 5  result = 0  i'm ok

02/01/2024  order 11 - forniture 7 - stock 0 (iused 5 yesterday)  result = 4 (i need to retrive 4 bags)

can somebody help me?  i have more product but max 1 row for product/day combination 

[v-yifanw-msft]

Hi @Alphacom ,

Thank you @Greg_Deckler  for your prompt reply. I have created a sample data for you to help you with your problem. You can follow the steps below:

1.Add a new column.

Daily Stock Requirement = 
VAR _date = 'Table'[date]
VAR _product = 'Table'[product]
VAR CumulativeOrder =
    CALCULATE (
        SUM ( 'Table'[order_qty] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] <= _date
                && 'Table'[product] = _product
        )
    )
VAR CumulativeForniture =
    CALCULATE (
        SUM ( 'Table'[forniture_qty] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] <= _date
                && 'Table'[product] = _product
        )
    )
RETURN
    CumulativeOrder - CumulativeForniture - 'Table'[stock_qty]

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Alphacom Recrusive calculations are generally a problem and can't be done. However, this looks more like a calculated column that leverages a previous value. I am not certain though how you know you used 5 stock_qty. Bit confused by the data. But here is a general solution.

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
 ( __Current - __Previous ) * 1.