Cumulative stock repeating values on days with no movement

[Anonymous]

Hi guys,

I ask for your support to guide me with the following problem:

I have a table of materials with their stocks, to calculate them they must be cumulative (and this works correctly), but now the problem occurs when I am asked to keep the stock on the days when there has been no movement. Here is an example:

Current scenario:

Desired scenario:

As we can see on January 1, 2016 there were movements, and then there was another on January 5, but on days 2, 3 and 4 there were no movements so the stock is what happened on January 1, and So on the days in which there was no movement should be placed the stock of the last day of movement, to thereby be able to perform the average of the day, as shown in the 2nd image.

Thank you in advance for your support.

Greetings.

[effinty2112]

Hi Juan,

In the data you have posted I can't find the field giving the Stock or Enero. What field should be used?

the fields given are:

Almacén

Año

Centro

Clase de movimiento

Ctd.en UM entrada

Día

Documento material

Fe.contabilización

Fecha

Importe ML

Material

Mes

Moneda

Periodo EM

Posición doc.mat.

Un.medida de entrada

Kind regards

Andrew

[antoniotiman]

Hi Juan,

where is Stock Field in Your attached file ?

[Anonymous]

The stock is the accumulated sum of the field: Ctd.en UM entrada

Regards.

[Anonymous]

The stock is the accumulated sum of the field: Ctd.en UM entrada

Regards.

[Anil_Babu_Samineni]

May be create master calendar for "Fe.contabilización" that date and use the same. and do missing values for dates

Fill in missing dates till today

[Anonymous]

Hi Anil Babu,

But as I can repeat the last movements in the following days in which they have not had movement.

Thanks,

Regards.

[sunny_talwar]

May be this

Only({1<Fe.contabilización = {"$(='>=' & Min(Fe.contabilización))"}>} Aggr(RangeSum(Above(Sum({<Año,Mes>}[Ctd.en UM entrada]) + Avg({1} 0), 0, RowNo())),(Fe.contabilización,(NUMERIC,ASCENDING))))

[Anonymous]

Hi stalwar1‌

Your proposal is very close to what I need, but what I should finally have is that when selecting a month (for example January) and the material code: 02-00002306, it should show me the following value: 63.60

This should be reflected in the following graph:

Thanks,

Regards.

[sunny_talwar]

How did you arrive at 63.60?