Forum Discussion

Francisco77's avatar
Francisco77
Copper Contributor
Oct 28, 2023

Formula to find number

Hi Community,   I have this table that shows values ​​by date. How could I generate a formula in which, indicating a start date, it shows me the date on which the accumulated sum of values ​​reache...
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Nov 01, 2023

Francisco77 

I recommend tabling the source data to set yourself up with dynamic items and then do some accumulation:

 

=LET(
    discard, XMATCH(DateInput, Table1[Date]) - 1,
    Values, DROP(Table1[Value], discard),
    Dates, DROP(Table1[Date], discard),
    Accumulate, LAMBDA(a, v, a + v),
    AccArray, SCAN(0, Values, Accumulate),
    XLOOKUP(100, AccArray, Dates, "", -1)
)

 

Resources