Cumulative Sum with Replacing Values

[lanecarrier]

Hello,

I am trying to create a column that calculates a rolling sum of test values but replaces a value when a specific id gets retested.  For reference, each id gets tested once per year and i need a rolling sum of latests tests so to speak.

API_NO is the id column and END_DATE is the test date with BOE_Potential as my value.  I am currently trying to do this with two calculated columns:

Cumulative Well Test =

CALCULATE (

SUM(dv_pwell_result_v[BOE_Potential]),

ALL(dv_pwell_result_v),

dv_pwell_result_v[last_test_date] >= EARLIER(dv_pwell_result_v[END_DATE]),

dv_pwell_result_v[API_NO] = EARLIER(dv_pwell_result_v[API_NO])

)

and

last_test_date =

CALCULATE(

MAX(dv_pwell_result_v[END_DATE]),

FILTER(dv_pwell_result_v, dv_pwell_result_v[API_NO] = EARLIER(dv_pwell_result_v[API_NO]))

)

I seem to be missing somthing. Any help is appreciated!

[amitchandak]

@lanecarrier , This seem fine , but it may be less than equal too

Cumulative Well Test =
CALCULATE (
SUM(dv_pwell_result_v[BOE_Potential]),
(dv_pwell_result_v),
dv_pwell_result_v[last_test_date] <= EARLIER(dv_pwell_result_v[END_DATE]),
dv_pwell_result_v[API_NO] = EARLIER(dv_pwell_result_v[API_NO])
)

No need for all in the column.

You can also consider a measure with date table

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[lanecarrier]

Ok,

Thanks for the link!  I was able to get a rolling sum going with calculated column:

Cumulative Well Test =

CALCULATE(

SUM(dv_pwell_result_v[BOE_Potential]),

FILTER(

ALLSELECTED(dv_pwell_result_v),

dv_pwell_result_v[END_DATE] <= EARLIER(dv_pwell_result_v[END_DATE])

)

)

This yields the following:

The remaining issue is to replace a value in the sum with newer data as entities are retested. 

For example, the top 3 lines all have the same API_NO (which is equivalent to id) and the value in the rolling sum for that entity should be replaced with the latest BOE_Potential (test value).