Calculate Running total from a given date onwards

[ventura]

Hi everyone!

Thank you in advance for your help on this one. I have been going a bit crazy with this one and after 6 hours trying to get it to work I point here to the experts for help and support.

I am trying to write a calculated column (Overdue_recovery) in power bi desktop that, for each customer (UID) essentially calculates a running total of payments (Cuota+Penal) received up to the current evaluated row (fecha_vencimiento) AND after the first date on the Default_date column column in the same table.

My best effort is the syntax below but it yields the incorrect result:

Overdue_Recovery =  CALCULATE(SUM(TablaCuotas[Cuota+Penal]);FILTER(ALLEXCEPT(TablaCuotas;TablaCuotas[UID]);TablaCuotas[paid_at]<=EARLIER(TablaCuotas[fecha_vencimiento]) && TablaCuotas[paid_at]>=EARLIEST(TablaCuotas[Default_date]));TablaCuotas[Pagada]="Si")

I highlight the key parameters on the picture attached and also upload an excel with the expected output.

I have tried to use the && on the FILTER argument but the output is not right.

Please help

Thank you!

[v-yulgu-msft]

Hi @ventura,

I know that you want to get the accumulative sum of Cuota+Penal per UID. However, I could not understand how to compute the running value. For example, why should the first 6 rows be 0? And could you explain how to calculate the highlighted value?

Besides, I noticed that there existing some blank rows in [paid_at] and [Default_date], then, for those blank rows, how to compare them?

Regards,

Yuliana Gu

[ventura]

Hi @v-yulgu-msft,

Thanks for your note!

Yes, certainly - the need is to calculate the running total but for payments received but only after the date of first default.

The table shown is basically a payment schedule table showing when different loan installments are due, the date when each installment was actually paid, the date when the installment went into default and the amount due for each installment.

In the example below, the UID shown first went into default on 01/03/2017 and thus the sum needs to only consider payments received after that date; that´s why it is 0 prior to that due date. The date of payment is shown on the column paid_at. 

With regards to blank rows in [paid_at] column it basically means that the installment has not yet been paid; as is the case with any installments after 01/02/2017 in the example shown. The blanks in the [Default_date] mean that the particular installment was not in default.

Thank you!

Best

Ventura 

[ventura]

Hi everyone,

So I have kept at it and found a way that makes it work for this particular case (UID), but more importantly it points in the direction of where the problem may lie:

If I specify a certain date then it works, as follows:

Overdue_Recovery = CALCULATE(SUM(TablaCuotas[Cuota+Penal]);FILTER(ALLEXCEPT(TablaCuotas;TablaCuotas[UID]);TablaCuotas[paid_at]<=EARLIER(TablaCuotas[fecha_vencimiento]) && TablaCuotas[paid_at]>=date(2017;03;01));TablaCuotas[Pagada]="Si")

But for some reason I can´t get it to pick the first date from [Default_date] - which is 2017/03/01 . What syntax can I use to return the first date on that column? worth mentioning that [Default_date] is a calculated column using dax and not from power query.

Thank you!