add a new column to accumulate a vlaue in a column specifying filters from another column

[Anonymous]

I have a ta ble of transactions(events) for a list of items. Each transaction (event) is assgined a value .  This value is accumulated for each item. the business logic requires that when the accumulated value of a particular item (let's say item 'A')  exceeds a threshold, this triggers an action from the user.

Now I need to see the date at which an item had accumulated enough values  to exceed the threshold (of let's say '2').

Note that there is a time at which a counter resets. and the accumulated values is only summing the values between the reset date and current event date. So I need to get the values of ??? as shown in the table below.

I thought to create a new column in the first table to list the accumulated values for each event date and then pull the date at which the accumulated value exceeded the threshold to the second table. But unfortunately, I couldn't add this column. Can you help me add a column that accumulated the values of the counter for each item between the event date and the counter reset date.

thanks.

Ali

[v-frfei-msft]

Hi @Anonymous ,

Sorry I cannot get you, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

thnaks for reaching out to help. I don't own the data and I cannot unfortunatley share it. Bad for me. 

I'll try to rephrase my question, to make it less confusing. 

basically, the problem boils down to adding a new column. This new column holds the summation of values from another column up to this row. so, if i have the following table, I need to calculate the new column to hold the running summation.

Value     Date                   (new column to hold the running summation)           

1            1 Jan 19                             1

2            19 Feb 19                           3   

2            23 Apr 19                           5   

1            30 Jun 19                            6 

4            1 Aug 19                            10

2            1 sep 19                             12

Then I need to report the date at which the new column value is 10 (in this case, 1 August 19).

Thank you. 

Ali

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can create what if parameter firstly.

Atfer that,  create a calcualted column to get the running total.

running = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))

Then we can get the date which we want.

Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[running] = Parameter[Parameter Value]))