Cumulative Loss Curves

[rawiswarden]

Hello,

I am trying to do a calculation in Power BI but I am unsure how to structure it.  

I need to calculate conditional cumulative losses and balances over time.  The X axis is months since origination, which is Duration in the below  table.  I have to divide the cumulative loss (Loss column) by the starting balance (Balance).

I then sum based on Duration and Age to determine the balances that have been outstanding for a given period of time and their associated losses.  Ultimately I am producing a hazard curve.

My goal is to allow the user to drop in any dimension and produce the relevant curves for that classifier. 

I can't figure out how to generate an index value of time (Months outstanding) and then do SUMX based on those calculations.  I am trying to learn DAX and not create the script in Python.

Any help is appreciated!

The Excel fromulas used are below.  I couldn't figure out how to attach the sheet.

[v-lili6-msft · Accepted solution]

hi @rawiswarden 

You could use this formula to create a new table as below:

New Table = 
ADDCOLUMNS (
    SELECTCOLUMNS ( 'Table', "Time Period", 3 * 'Table'[ID] ),
    "Balance", CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER ( 'Table', 'Table'[Age] >= [Time Period] )
    ),
    "Cumulative Loss", CALCULATE (
        SUM ( 'Table'[Loss] ),
        FILTER (
            'Table',
            'Table'[Age] >= [Time Period]
                && 'Table'[Duration] <= [Time Period]
        )
    )
)

and then add a calculated column as below:

Percent = 'New Table'[Cumulative Loss]/'New Table'[Balance]

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[rawiswarden]

Hello @v-lili6-msft !

Thanks for the example.  You solved a couple of my issues in terms of structuring the table.  I didn't know I could multiply the ID like that, for example.

Now, say I want to add category to the legend of a line graph which shows the percentage calculation for each.  Is this possible?  I'm not sure how to make these calculations conditional in this manner.   Do I just need to create seperate visuals and filter the data on each visual?

[rawiswarden]

Hello,

I believe I figured this out.  I was trying to avoid manually creating a table, so if anyone can explain how to do this automatically I would be very happy.

I used "Enter Data" to create a new table and manually entered each time period (1 to N).  I think I stopped at 50.  This was the step I was trying to do in DAX.  I tried with GENERATESERIES  but it does not appear to work in a way that would work.

I created measures using the sort of calculations above and then linked the fact table used in the calculations to the dimension tables.  The table where the calculations occur is not linked to anything.