Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
lanecarrier
Frequent Visitor

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 need a rolling sum of latests tests so to speak.

 

lanecarrier_0-1676323275420.png

 

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!
2 REPLIES 2
amitchandak
Super User
Super User

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

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:
lanecarrier_0-1676392450805.png

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

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.