Cumulative Sum based on dates in Power BI

[Saarek]

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 solution]

Hi @Saarek ,

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])&&[TransactionDate]<=MAX('Table'[TransactionDate])))

Then create another measure to get the MAX cumulative sum.

MAX Cumulative = MAXX(ALLSELECTED('Table'),[Cumulative])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Saarek]

Thank you very much, far simpler than I expected it to be. This works perfectly.