Running Total Reset based on Column Attribute

You did it! Great job Lydia! Anonymous

Now to clarify for anyone else, what she did with Measure 4 (renamed to LC Date in my image here) is make every row have the date of the last Count (a reset date) therefore creating a range to sum.

Here it is:

LC Date = MAXX(FILTER(ALLSELECTED(Movements), Movements[EntryType]="Count" && Movements[Time] <= MAX(_Date[Date])),Movements[Time])

Now one thing that was causing a problem for me was that if you are using a date table as the time relationship, the datatypes always needs to be date or datetime not text. My whole model is a bit involved so I posted my relationship diagram below.

Then with the Run Stock measure:

Run stock = CALCULATE(SUM(Movements[Mvnt Quantity]) - [Tot BOM Sales], DATESBETWEEN(_Date[Date],[LC Date],LASTDATE(_Date[Date])))

Its pretty clear, the key being DatesBetween. Using the _Date table as the base you plug in that Last Count date and it works.

Tot BOM Sales is coming from my itemProfile, which is like an ingredients list or Bill of Materials. That amount is also date based and its subtracted from the movement quantity. - in the real world there would be more sales and the counts would be close to the 'theoretical' calculated running stock - giving managers a benchmark on how much food is being wasted or underreported.

Fantastic job Lydia you made my day.

Hi, 

I have an scenario very simmilar but with a very important difference: the date field is a date time field with a time stamp, so I can have more than one value for the same date, and I have to reset more than one time in the same date.

The rest is almost the same but the datesbetween doesn´t work.

In my case the reset condition is Hoja1[TIPO]="II" 

I´ve tried this

Reset = MAXX(FILTER(ALLSELECTED(Hoja1);Hoja1[TIPO]="II" && Hoja1[FEC_HOR_ALTA]<=MAX(Hoja1[FEC_HOR_ALTA]));Hoja1[FEC_HOR_ALTA])

STK ACT 2 = CALCULATE(sum(Hoja1[Cantidad Corregida]);filter(Hoja1;Hoja1[FEC_HOR_ALTA]>[Reset] && Hoja1[FEC_HOR_ALTA] <= MAX ( Hoja1[FEC_HOR_ALTA])))

But is not working.

You may see that STK ACT has the classical cummulative pattern and STK ACT 2 the modified one. And I used the name Reset instead of Messure 4

Here is the link to this file:

https://lhcampus-my.sharepoint.com/:u:/g/personal/felix_avendano_lhcampus_onmicrosoft_com/EX5Xi8uh0FtDt8eOvrhiSBUBB9MSqbN2v8kM8y3oRXchng?e=RtHc33

Thanks for any help