topic Re: Compound Fees in DAX Commands and Tips

Compound Fees

Brenoo — Fri, 15 Jul 2022 19:03:59 GMT

YEAR	MONTH	PREVIOUS VALUE	VALUE	REND	REND%	REND TOTAL %
2018	JAN	R$ 1.685.515,06	R$ 1.667.048,37	R$ 6.533,31	0,39%
2018	FEV	R$ 1.667.048,37	R$ 1.615.559,21	R$ 7.911,80	0,49%
2018	MAR	R$ 1.615.559,21	R$ 1.623.287,84	R$ 7.728,63	0,48%
2018	APPR	R$ 1.623.287,84	R$ 1.630.633,74	R$ 7.345,90	0,45%
2018	MAY	R$ 1.630.633,74	R$ 1.638.727,03	R$ 8.093,29	0,50%
2018	JUN	R$ 1.638.727,03	R$ 1.646.867,14	R$ 8.140,11	0,50%

Hello, here is my problem:

1. PREVIOUS VALUE, VALUE, REND, REND% are MEASURES.

2. For REND TOTAL % I need to copound the fees from REND%.

Since PRODUCT X only works with table, how do I do that with a measure?

Re: Compound Fees

PaulOlding — Fri, 15 Jul 2022 21:15:02 GMT

Hi Brenoo

The table you'd want to iterate in your PRODUCTX is the YEAR and MONTH columns included in the visual.

The ALLSELECTED function gives you the filters applied to the visual as a whole.

So, something like the below, that uses a Date table with Year, Month and YearMonth columns:

REND TOTAL % = VAR _YearMonths = FILTER( ALLSELECTED('Date'[Year],'Date'[Month], 'Date'[YearMonth]), 'Date'[YearMonth] <= MAX('Date'[YearMonth]) ) VAR _Result = PRODUCTX( _YearMonths, [REND%] ) RETURN _Result

Re: Compound Fees

Brenoo — Sat, 16 Jul 2022 23:16:27 GMT

Hello, first of all, thanks for your help. I forgot to let you know that Date Year and Date Month are colums from a Calendar Table that is related to a colum "DATE" type on the table INVEST. Tried to addapted your code to:

Rend_Total % = VAR _YearMonths =
FILTER(
ALLSELECTED(CALENDARIO[Date].[Date]),
CALENDARIO[Date].[Date] <= MAX('INVEST MOV'[DATA])
)
VAR _Result =
PRODUCTX(_YearMonths, [Rend %])
RETURN
_Result

It did not work.

here are all my code related to [Rend%]

Rend % = CALCULATE(
DIVIDE([Rend], Medidas[Saldo_Rend], 0),
'INVEST MOV'[DATA]
)

Saldo_Rend = CALCULATE(
Medidas[Saldo]-[Rend],
'INVEST MOV'[DATA]
)

Rend = CALCULATE(
Medidas[Saldo]-Medidas[Aplic]-Medidas[Aporte]-Medidas[Resgate]-Medidas[Liquidado]-Medidas[Saldo_Ant],
'INVEST MOV'[DATA]
)

Saldo = CALCULATE(
SUM('INVEST MOV'[VALOR]),
MOVTIPO[Movtipo.Nome]="SALDO"
)

For the rest of measueres Aplic, Aporte, Resgate, Liquidado, Saldo_Ant is the same as above just changing the filter MOVTIPO[Movtipo.Nome]="SALDO" to each.

Re: Compound Fees

Brenoo — Fri, 22 Jul 2022 16:55:52 GMT

If I link my original Power bi file and a Excell data may you help me? I really need this calc for my report.

Thanks