running total sum per category with category not in order

[ejoyce5]

Within a table I am trying to get the rollling sum of each member, where the rolling sum is ordered by sale date. This is important to do a rolling sum vs. an overall total for each member because I need to identify the sales date at which a member hit above a certain $ threshold. Ex: identify when a member hit $200 total $, 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ejsoccer5]

This worked for the running total measure, but now I am trying to pull the first date of when the member's rolling total > 20k. Like the below screenshot, this member hit above 20k on 2/1/2024, so I want in my output a single line for each member to show the minimum date they hit above 20k and the overall sum of paid amount per member. The minimum date should not change once the member hits above 20k. I created a measure that identified when rolling paid amount > 20k then 1 else 0. When I filter on this measure = 1, I get those dates where the rolling paid amount is greater than 20k but I only want 1 row returned for each member with the minimum date when rolling paid amount > 20k.

[Ashish_Mathur · Accepted solution]

PBI file attached.  I have kept the threshold as 110.

Hope this helps.

[JamesFR06]

Hi @ejoyce5 

Please try this code :

RTSD =

var Mem=SELECTEDVALUE(RT2[Menber])

var Datemaxi=SELECTEDVALUE(RT2[Sell Date])

return

CALCULATE(sum(RT2[Amount]),ALL(rt2),RT2[Menber]=mem&&RT2[Sell Date]<=Datemaxi)

[ejoyce5]

Unfortunately this doesn't get to where I want to be. I have to have a column that shows the running total per member ordered by date, because what I am trying to do is flag for each row where a members total amount >= $200 and take the MIN date with the flag to show the date at which a member hit the $200 threshold