Decreasing running total

[valeriydik]

Hi everyone!

I'm developing visual to calculate loss given default (LGD) for the 91+/181+ default generations (separately). I'm stuck on the metric algorithm itself. In Excel, this is currently done as inflexible and static formula (see pic 1).

In Power BI, I want to slice-n-dice it by product line and LGD type (91+ or 181+). What is more, a number of "columns", i.e. # of months since default, may vary and, definitely, it will also increment over time 🙂 So, it must be a single metric, probably, accompanied by an intermediate column/metric.

How it can be done? I failed to implement running total and I also feel that it alone does not do the job.

I already did some data preparation - see pics 2 and 3.

Any help is appreciated

[valeriydik]

Community is my last hope - I have been struggling with it for months!

[Vvelarde]

@valeriydik

Hi can you share a Dummy PBIX and a XLS (to review the formula and what rows and columns use in the visual?

You can upload to dropbox, drive or another and share the link.

Regards

Victor

[valeriydik]

@Vvelarde,

the archive with files is here: LGD.zip

[Ashish_Mathur]

Hi @valeriydik,

I cannot understand how you arrived at the numbers in range C2:M13 of your Excel file.  They look like pasted special values.  Please share the underlying formulas.  Also, please explain the business logic of what you are trying to do.  What exactly is LGD?  What is the purpose of this exercise?

Thank you.