Forum Discussion

Brenoo's avatar
Brenoo
Regular Visitor
4 years ago

Compound Fees

YEARMONTHPREVIOUS VALUEVALUERENDREND%REND TOTAL %

2018

JAN

R$ 1.685.515,06R$ 1.667.048,37R$ 6.533,310,39% 
2018FEVR$ 1.667.048,37R$ 1.615.559,21R$ 7.911,800,49% 
2018MARR$ 1.615.559,21R$ 1.623.287,84R$ 7.728,630,48% 
2018APPRR$ 1.623.287,84R$ 1.630.633,74R$ 7.345,900,45% 
2018MAYR$ 1.630.633,74R$ 1.638.727,03R$ 8.093,290,50% 
2018JUNR$ 1.638.727,03R$ 1.646.867,14R$ 8.140,110,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?

3 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    4 years ago

    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

     

     

    • Brenoo's avatar
      Brenoo
      Regular Visitor
      4 years ago

      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.

       

       

    • Brenoo's avatar
      Brenoo
      Regular Visitor
      4 years ago

      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