topic Re: Help with interest meassure over time in DAX Commands and Tips

Help with interest meassure over time

jotapece — Tue, 31 Mar 2020 13:25:34 GMT

Hello all.

I have a bad time trying to get that DAX meassure and I need a hand so, I have this data and calcs in excel:

The meassure I want to get is [Interest Total].

[Interest Total] = [Interest1] + [Interest2]

[Interest1] =

(The SI function in IF function in spanish).

[Interest2] =

Note: [Interest2] always SUM all the previous interest (1 and 2). Then affect with correspond rate to get the result.

[Year - Month]: is column in the calendar table with all the consecutive dates.

[Accumulated Profit], [Active Rate] and [Passive Rate] are a meassures.

I could calculate [Interest1] in a meassure (thinking in auxiliar meassure to get the total), but I can't figure out how get [Interest2)].

Any Ideas?

Regards,

Re: Help with interest meassure over time

Greg_Deckler — Tue, 31 Mar 2020 14:21:20 GMT

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Help with interest meassure over time

jotapece — Mon, 06 Apr 2020 21:09:41 GMT

I'm struggling with this for days!

I write this meassure in Dax Studio:

EVALUATE VAR Paso1 = FILTER ( SUMMARIZE ( Calendario; Calendario[idxFecha]; "R"; CALCULATE ( [Resultado Acumulado] - [Resultado]; Centros_Costo[CC] = "2500" ) ); [R] <> BLANK () ) VAR Paso2 = ADDCOLUMNS ( Paso1; "IntR"; [R] * IF ( [R] >= 0; [Tasa Pasiva]; [Tasa Activa] ) ) VAR Paso3 = ADDCOLUMNS ( Paso2; "Int2"; VAR Suma = SUMX ( FILTER ( Paso2; [idxFecha] < EARLIER ( [idxFecha] ) ); [IntR] ) RETURN Suma * IF ( Suma >= 0; [Tasa Pasiva]; [Tasa Activa] ) ) VAR Paso4 = ADDCOLUMNS ( Paso3; "Int3"; VAR Suma = SUMX ( FILTER ( Paso3; [idxFecha] < EARLIER ( [idxFecha] ) ); [Int2] ) RETURN Suma * IF ( Suma >= 0; [Tasa Pasiva]; [Tasa Activa] ) ) VAR Paso5 = ADDCOLUMNS ( Paso4; "IntTotal"; [IntR] + [Int2] + [Int3] ) RETURN Paso5

[IdxFecha] = Year * 100 + Day

This are the results:

This are correct results for [IntTotal].

Now I wat go back to Power BI and....

So, the total is correct, but I lost my data lineage.

The meassure [Interes] is all the code at first, but the last line change to SUMX( Paso5; [IntTotal] ).

Any ideas?

Thanks!