Running total with Reset

[Anonymous]

Hi there,

I am trying to create a column that has a running total, but resets if a column has a 1 (if the column has a value of 0, then the score does not reset). I followed this previous post, but I am getting something odd. 

https://community.powerbi.com/t5/Desktop/Running-Total-Reset-based-on-Column-Attribute/m-p/251130

The actual line items with dates show the correct running total (with reset). however, the dates that do not have activities show a value of 1090 (which happens to be the running total on 7/29/19). 

For example, based on the data table below, Dates of 12/31/18 to 1/22/19 show a value of 1090, which causes my line chart to be incorrect. 

The sample data set is below. What happens is that I choose a single customer id, and have a list of all the activities that the customer had on our website. Each activity is assigned a specific score and these scores are aggregated until they need to be reset, which is denoted by the "Reset score" column (1=reset, 2=no reset).  The "Running score with reset" is what I'm trying to get, but I'm getting scores of 1090 for all the dates that are not listed in the table. . 

Here are the calculations I am using:

Running Score =

CALCULATE(

sum(Table1'[Activity Score]),

FILTER(

ALLSELECTED(Table1),

Table1[Activity Date] <= max(Table1[Activity Date])))

Max Date = maxx(

FILTER(

ALLSELECTED(Table1),

Table1[reset lead score] = 1 && Table1[Activity Date] <= max(Table1[Activity Date])),

Table1[Activity Date])

Running Score with Reset =

CALCULATE(

sum(Table1[Activity Score]),

DATESBETWEEN(

Table1[Activity Date],'Measure Table'[Max Date],LASTDATE(Table1'[Activity Date])))

Data set:

Id	ID Index	Activity Date	Activity Score	Running Score	reset score	Max Date	Running score with reset
N0EAJ	1	12/30/2018	50	70	1	12/30/2018	70
N0EAJ	2	12/30/2018	20	70	0	12/30/2018	70
N0EAJ	3	1/23/2019	50	120	1	1/23/2019	50
N0EAJ	4	1/24/2019	50	220	0	1/23/2019	100
N0EAJ	5	1/24/2019	50	220	0	1/23/2019	150
N0EAJ	6	2/12/2019	50	270	1	2/12/2019	50
N0EAJ	7	2/17/2019	50	390	1	2/17/2019	120
N0EAJ	8	2/17/2019	50	390	0	2/17/2019	120
N0EAJ	9	2/17/2019	20	390	0	2/17/2019	120
N0EAJ	10	2/26/2019	50	490	1	2/26/2019	100
N0EAJ	11	2/26/2019	50	490	0	2/26/2019	100
N0EAJ	12	3/5/2019	50	540	1	3/5/2019	50
N0EAJ	13	3/11/2019	50	590	1	3/11/2019	50
N0EAJ	14	3/19/2019	50	640	1	3/19/2019	50
N0EAJ	15	3/21/2019	50	690	1	3/21/2019	50
N0EAJ	16	3/22/2019	50	740	0	3/21/2019	100
N0EAJ	17	3/24/2019	50	790	0	3/21/2019	150
N0EAJ	18	3/28/2019	50	840	1	3/28/2019	50
N0EAJ	19	4/24/2019	0	840	0	3/28/2019	50
N0EAJ	20	5/8/2019	50	890	1	5/8/2019	50
N0EAJ	21	6/4/2019	50	940	1	6/4/2019	50
N0EAJ	22	6/7/2019	50	990	0	6/4/2019	100
N0EAJ	23	7/22/2019	50	1040	1	7/22/2019	50
N0EAJ	24	7/29/2019	50	1090	0	7/22/2019	100

This is a sample of the resulting table I'm getting with my current calculations. 

Any assistance would be great! 

Thank you. 

[Anonymous]

This is a sample of the data I'm getting when I run the above set of queries. 

[v-lili6-msft]

hi, @Anonymous 

I have test on my side, it works well on my side.

Could you please share your sample pbix file for us have a test? there may be something wrong in other.

Best Regards,

Lin

[Anonymous · Accepted solution]

Thanks for your input. I was able to figure out. 

Basically what was happening it was calling the customer ID from a different table instead of within the same table. Once I did this minor change, it worked. 

(Like you, I tried it with the simple table I sent in my original post, and it worked with the same calculations and measures). 

Thanks!