Forum Discussion

ryanLewis's avatar
ryanLewis
Frequent Visitor
8 years ago
Solved

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 ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

ryanLewis's avatar
ryanLewis
Frequent Visitor
8 years ago

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.