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

avendanof's avatar
avendanof
Helper I
7 years ago

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