hello,
Here's the step-by-step approach:
Assumptions:
You have a table named "Transactions" that contains the columns: "Amount," "Lapse Yr," and "Sponsor."
Step 1: Create a calculated column for Cumulative Total
First, you need to create a calculated column that calculates the cumulative total for each combination of Lapse Yr and Sponsor. This can be done using the FILTER and EARLIER functions.
Cumulative Total =
CALCULATE(
SUM('Transactions'[Amount]),
FILTER(
ALL('Transactions'),
'Transactions'[Sponsor] = EARLIER('Transactions'[Sponsor]) &&
'Transactions'[Lapse Yr] <= EARLIER('Transactions'[Lapse Yr])
)
)
Step 2: Create a calculated column for BEY-Cumulative Total
Next, you can create another calculated column to identify the first positive value in the Cumulative Total and show the corresponding Lapse Yr for the Sponsor.
BEY-Cumulative total =
VAR CumulativeTotal = [Cumulative Total]
VAR PositiveTotal =
CALCULATE(
MIN('Transactions'[Lapse Yr]),
FILTER(
ALL('Transactions'),
'Transactions'[Sponsor] = EARLIER('Transactions'[Sponsor]) &&
[Cumulative Total] > 0
)
)
RETURN
IF(CumulativeTotal > 0, PositiveTotal, BLANK())
In this formula, we use a variable CumulativeTotal to store the Cumulative Total for each row and then find the first positive Cumulative Total value for the Sponsor using the FILTER function.
Step 3: Result
Now, your table should have two additional calculated columns: "Cumulative Total" and "BEY-Cumulative total." The "BEY-Cumulative total" column will show the corresponding Lapse Yr for the Sponsor when the Cumulative Total is greater than zero.
Remember, these calculated columns will be computed for each row in the table, so the result will be dynamic based on the data in the "Transactions" table.
Note: If you want to avoid using calculated columns and want to show these values as measures, the approach will be slightly different. Let me know if that's the case, and I can provide the measure-based approach as well.
