Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Value in cell calculated on previous cell value


MêsEnt0M1M2M3M4M5M6M7M8M9M10M11M12M13M14M15M16M17M18M19M20M21M22M23M24M+
01/01/20238379476737452727262126212324192322201719191716161619
01/02/20237268714445314024272620252123241923212017191917161619
01/03/20235857615926402636232426202321222219222119171719171619
01/04/20235248495740243925362223261922212122192221191716191719
01/05/20238567674729392223252521202318192121201920201917161619
01/06/202377                         
01/07/202379                         
01/08/202362                         
01/09/202350                         
01/10/202350                         
01/11/202374                         
01/12/202335                         


Hi everyone!

I have the next table (example is above. First row has Headers)

The table represents number of a product units by number of months it stays in stock. So ENT shows us how much product comes into the stock. 0M - how much units remains before the end of the first month, 1M - how much units remains in stock more than 1 month but less then 2; 2M - more then 2 months but less then 3 etc.
Values in the ENT column are defined. COEF - shows the proportion of the group that will remains in stock one more month.

The values up to previous month (Columns '0M', '1M' etc) are filled manually in the source.  Values for other months must be calculated like this:  Value of the previous month * coef.  So the June value is calculated on the base of the May value, the July value is calculated on the base of calculated June value, etc.
Coef = SUM(Column n+1[2st row : actual month row] SUM(Column n [1st row : actual month - 1 row])

Here is an exemple of desired result in excel (only the values must be calculated for each month until the end of the year)
Also Sum function will be flexivel such the the bottom cell of the interval will be linked to actual month (i.e. instead of =SUM(C2:C5) there will be something like = CALCULATE(SUM(C), FILTER(Datetable, Datetable[year] = year_now &&  Datetable[month] = month_now - 1))

 

 

How do I create a measure or a column that helps me with this?
 

6 Replies