I'm currently working on a project where I need to calculate cumulative loss in Power BI, but I'm encountering issues with the implementation. Here’s the setup:

I have a table (let's call it Month Loss Table) that looks like this:

The columns are time period (TP) where TP >= 0 and TP <= 15. Rows are Category = R, or Category = NR.

I'm currently working on a project where I need to calculate cumulative loss in Power BI, but I'm encountering issues with the implementation. Here’s the setup:

I have a table (let's call it Month Loss Table) that looks like this:

The columns are time period (TP) where TP >= 0 and TP <= 15. Rows are Category = R, or Category = NR.

I need to create a Cumulative Loss Table that follows this logic:

For TP 0, the cumulative value is the same as the Month Loss Table value. For TP > 0, the cumulative value is calculated as: Cumulative value = (Cumulative value of previous TP) + (Month Loss value for the current TP).

The expected output looks like this:

For TP 0, the values are the same as above table.

For TP 1, 9+1 = 10.

For TP 2, 14+10 = 24.

For TP 3, 19+24 = 43

...etc.

My Approach So Far

I created two measures for the Month Loss Table using DAX, and they work correctly.

Loss per Acct = DIVIDE(SUM(LossAmount), SUM(NumberOfAccounts), 0)

TotalLossPerAcct = 
VAR 
R_Total = 
CALCULATE(
    [Loss per acct],
    Sheet1[Category] = "R"
)

VAR
NR_Total =
CALCULATE(
    [Loss per acct],
    Sheet1[Category] = "NR"
) / 0.5

RETURN
SWITCH(
    SELECTEDVALUE(Sheet1[Tag]),
    "R", R_Total,
    "NR", NR_Total
    )

For the Cumulative Loss Table, I tried creating a measure like this:

Cumulative_Loss =

   VAR CurrentTP = SELECTEDVALUE(MyTable[TP])

   VAR CurrentCAT = SELECTEDVALUE(MyTable[Category])

   RETURN

       SUMX(

           FILTER(

               ALL(MyTable),

               MyTable[TP] <= CurrentTP &&

               MyTable[Category] = CurrentCAT

           ),

           [TotalLossPerAcct]

       )​

However, this approach doesn't produce the correct cumulative values. The results are either far too large or don’t follow the cumulative logic.

Challenges

• Context Filtering: Ensuring that the calculation respects the Category (R vs. NR) and only sums values for the specific time period range.

• Correct Aggregation: The results don't align with the expected cumulative logic where each tenure adds to the previous one.

What I Need

• A working DAX measure for Cumulative Loss that:

Uses the values from the Month Loss Table. Calculates cumulative loss correctly based on the described logic. Avoids incorrect aggregation.

EDIT:

I'm attaching screenshot of sample data.

enter image description here

Category is both NR and R for timeperiod (TP) which is from -6 to 24, including 0. Each TP has date from 202211 to 202410. Num_of_acct is always positive, mainly large numbers. Loss_amt is from -20,000 to 20,000,000, including sometimes 0. There are about 10 other columns that are not relevant to the current problem. Let me know if I can provide any other data.

• powerbi
• powerbi-desktop

• Is your table the raw data or a matrix visual? if it's a matrix visual, pls provide the sample data. – Ryan Commented yesterday
• Hi @Ryan, I have attached a screenshot of the data along with a description. Hope that helps. – luffy627 Commented yesterday

2 Answers 2

Use the measure:

Cumulative_Loss = 
CALCULATE (
    [TotalLossPerAcct],
    WINDOW (
        1, ABS,
        0, REL,
        ALL ( MyTable[TP] )
    )
)

you can try to change the row and column field and use New visual creation