Forum Discussion

lanecarrier's avatar
lanecarrier
Frequent Visitor
3 years ago

Cumulative Sum with Replacing Values

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 n...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

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's avatar
lanecarrier
Frequent Visitor
3 years ago

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).