Cumulative Sum based on dates in Power BI

Question

I have a set of claims data. This data only has three columns, TransactionDate, ClaimNumber, ReserveNet.

I have been asked to pin point the maximum ReserveNet amount based on the dates grouped by claim number. Lets say we have a claim with a ClaimNumber of A1 and we have 6 transactions for this claim.

01/01/2022 - ReserveNet £1000

03/01/2022 - ReserveNet £3000

05/01/2022 - ReserveNet £5000

06/01/2022 - ReserveNet -£1000

09/01/2022 - ReserveNet £2000

25/01/2022 - ReserveNet -£10000

I know that the maximum ReserveNet was achieved on 09/01/2022 with a cumulative sum of £10,000.

I have code that works fine for a standard cumulative view, but cannot work out how to get it to stop at the maximum amount.

One step I tried was duplicating the table in PowerBI, sorting the ClaimNumber and TransactionDate (desc) and adding in a custom column:

= Table.AddColumn(#"Added Index", "Custom", each Table.AddColumn( #"Added Index", "CumulativeSum", each List.Sum( Table.SelectRows( #"Added Index", each [ClaimNumber] = [ClaimNumber] and [Index] <= [Index] )[NetIncurred] ), type number))

But that just brings back a result of "Table" within said custom column. Any help would be greatly appreciated.

anonymous · Accepted Answer

Hi&nbsp;Saarek&nbsp;,
&nbsp;
Since you post in Power BI Desktop forum. Here's the solution using DAX.
You can create a measure to get the cumulative sum first.
Cumulative = CALCULATE(SUM('Table'[ReserveNet]),FILTER(ALLSELECTED('Table'),[ClaimNumber]=MAX('Table'[ClaimNumber])&amp;&amp;[TransactionDate]&lt;=MAX('Table'[TransactionDate])))
Then create another measure to get the MAX cumulative sum.
MAX Cumulative = MAXX(ALLSELECTED('Table'),[Cumulative])

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Cumulative Sum based on dates in Power BI

2 Replies

Related content

Year to date cumulative

Cumulative Total

Creating Cumulative Graphs

Cumulative Count by Date

cumulative sum by date and product