cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryanLewis
Frequent Visitor

Running Total Reset based on Column Attribute

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. 

pbi1.jpg

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

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@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])))

1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Microsoft
Microsoft

@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])))

1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

 

 

 

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.

 

 

solution.jpg

sol2.jpg

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors