Running Total Reset based on Column Attribute

[ryanLewis]

I've been struggling to implement this correctly. I have a cumulative running total for a stock item (Cusquena in this example). That part is working fine. What I need it to do however is reset to a specified value based on the attribute of another column. 

On September 01, the inventory is counted in the warehouse with a level of 8 Liters... 

On September 07, the company buys 10 more liters and now the warehouse has 18 Liters...

Now on September 10, the inventory is counted again and they count 6 Liters...

The running total should be reset to 6 Liters -- this is what I cannot get to work.

To give you context, the idea is with the Sales continuously reducing the stock level, the Inventory count sould be very close to the calculated running stock and the managers can see how much is being wasted, etc. 

In the final solution sales (Tot BOM Sales) would be subtracting the amount and applying that to the running total, I have that part working too but for simplicity sake I've omited that part.

How would I get this running stock to adjust to the last count and continue summing from that point?

This is my formula for Running Stock:

Run Stock = CALCULATE(SUM(Movements[Mvnt Quantity]), FILTER(ALL(_Date[DateMain]), _Date[DateMain] <= MAX(_Date[DateMain])))

I have found numerous examples getting a running stock, but nothing about reseting the summation point based on another column attribute. 

Thank you kindly in advance

[v-yuezhe-msft · Accepted solution]

@ryanLewis,

I make a test using the sample data you post, and create the following measures in the table.

Measure 4 = MAXX(FILTER(ALLSELECTED(Table4),Table4[First EntryType]="Count"&&Table4[DateMain]<=MAX(Table4[DateMain])),Table4[DateMain])

Run stock = CALCULATE(SUM(Table4[Mvnt Quantity]), DATESBETWEEN(Table4[DateMain],[Measure 4],LASTDATE(Table4[DateMain])))




Regards,
Lydia

[avendanof]

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/EX5Xi8uh0F...

Thanks for any help

[ryanLewis]

You did it! Great job Lydia! @v-yuezhe-msft

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.