Forum Discussion

Matthew_BR's avatar
Matthew_BR
Frequent Visitor
3 years ago
Solved

Formula Cascade Referencing Itself

Hi, I have a spreadsheet with the name of items, the size of deliveries available by date, the demand in the month and an index column

I'm trying to create a cascading column that subtracts the demand from the first available delivery and then takes the result and uses it to subtract from the next available delivery. That way I would be able to know which deliveries are necessary.


I created this formula, the problem that goes into a circular formula that BI does not accept.

 

 

Cascata = 
VAR Delivery = Table[Delivery]
VAR Demand = Table[Demand]
VAR Previous Index = CALCULATE (
    MAX(Table[Cascata]),
    FILTER (Table[Index] = EARLIER(Table[Index]) - 1 ))
RETURN
    IF(Table[Index] = 1, Demand - Delivery, Previous Index - Delivery)

 

 

Does anyone know an alternative to solve the problem?
Thanks to anyone who will look into this!

 

ItemDateSize of DeliveryDemandIndexWhat I need the table to do
AAA2023-08-01        10    201               10
AAA2023-08-02         8    202                2
AAA2023-08-03         5    203

               -3

Need Help
  • Matthew_BR's avatar
    Matthew_BR
    3 years ago

    I managed to solve it, in case someone in the future also has this doubt, I used this formula as a solution

     

    Cascata = 
VAR Delivery = Table[Delivery]
VAR Demand = Table[Demand]
VAR Previous Index = CALCULATE(
        SUM(Table[Delivery]),
        FILTER(Table[Index] < EARLIER(Table[Index])
RETURN
    IF(Table[Index] = 1, Demand - Delivery, Demand - Previous Index - Delivery)

     

    Instead of referencing the column itself, I used the variable and filter formulas to sum the values of previous deliveries. Then I subtracted from the total demand value

1 Reply

  • Matthew_BR's avatar
    Matthew_BR
    Frequent Visitor
    3 years ago

    I managed to solve it, in case someone in the future also has this doubt, I used this formula as a solution

     

    Cascata = 
VAR Delivery = Table[Delivery]
VAR Demand = Table[Demand]
VAR Previous Index = CALCULATE(
        SUM(Table[Delivery]),
        FILTER(Table[Index] < EARLIER(Table[Index])
RETURN
    IF(Table[Index] = 1, Demand - Delivery, Demand - Previous Index - Delivery)

     

    Instead of referencing the column itself, I used the variable and filter formulas to sum the values of previous deliveries. Then I subtracted from the total demand value