Dax juros compostos diários e mensais por contrato de empréstimo

Bruno Santos · Janeiro 31, 2023

Boa tarde, conto com a ajuda de vocês para solucionar a seguinte pendência.

Estou tentando criar uma medida para calcular o juros do saldo acumulado com base na tabela CDI diária, mas dependendo do tipo do contrato a taxa de juros pode mudar, exemplo 100%, 90%, 50% ou 20% do CDI.

Em anexo segue base de dados e arquivo exemplo pbix.

Desde já agradeço se alguém tiver alguma ideia sobre.

Dados.xlsx JUROS.pbix

Bruno Abdalla de Souza · Fevereiro 9, 2023

@Bruno Santos, veja se isso resolve:

Juros Acumulados v1 = 
VAR vContrato = MAX(CONTRATOS[CONTRATO])
VAR vTipoContrato = MAX(CADASTROS[Período])
VAR vPercCDI = MAX(CADASTROS[% CDI])
VAR vDataPrimeiroEmprestimo = 
    CALCULATE(
        MIN(DCalendario[Data]),
        ALL(DCalendario),
        FILTER(
            ALLSELECTED(CONTRATOS),
            CONTRATOS[VALOR] > 0
        )
    )
VAR vDataFimMesPrimeiroEmprestimo = EOMONTH(vDataPrimeiroEmprestimo, 0)
VAR vDataContexto = MAX(DCalendario[Data])
VAR vEmprestimos = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[VALOR] > 0,
        CONTRATOS[DATA] <= vDataContexto
    )
VAR vJurosAcumuladosEmprestimo = 
    SUMX(
        vEmprestimos,
        VAR vDataEmprestimo = CONTRATOS[DATA]
        VAR vJuros = 
            IF(
                vDataContexto = vDataEmprestimo,
                0,
                PRODUCTX(
                    FILTER(
                        ALL(DCalendario[Data]),
                        DCalendario[Data] <= vDataContexto && 
                        DCalendario[Data] >= vDataEmprestimo + 1
                    ),
                    VAR vJurosDia = IF(vTipoContrato = "MENSAL", CALCULATE(SUM(CDI[MÊS])), CALCULATE(SUM(CDI[CDI DIA])))
                    RETURN
                        (1 + vJurosDia * IF(vJurosDia <> 0, vPercCDI, 1))
                ) - 1
            )
        VAR vJurosAcumulados = CONTRATOS[VALOR] * vJuros
        RETURN
            vJurosAcumulados
    )
VAR vAmortizacoes = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[AMORTIZAÇÃO] > 0,
        CONTRATOS[DATA] <= vDataContexto
    )
VAR vJurosAcumuladosAmortizacao = 
    SUMX(
        vAmortizacoes,
        VAR vDataAmortizacao = CONTRATOS[DATA]
        VAR vJuros = 
            IF(
                vDataContexto = vDataAmortizacao,
                0,
                PRODUCTX(
                    FILTER(
                        ALL(DCalendario[Data]),
                        DCalendario[Data] <= vDataContexto && 
                        DCalendario[Data] >= vDataAmortizacao  + 1
                    ),
                    VAR vJurosDia = IF(vTipoContrato = "MENSAL", CALCULATE(SUM(CDI[MÊS])), CALCULATE(SUM(CDI[CDI DIA])))
                    RETURN
                        (1 + vJurosDia * IF(vJurosDia <> 0, vPercCDI, 1))
                ) - 1
            )
        VAR vJurosAcumulados = CONTRATOS[AMORTIZAÇÃO] * vJuros
        RETURN
            vJurosAcumulados
    )
VAR vJurosAcumuladosTotal = vJurosAcumuladosEmprestimo - vJurosAcumuladosAmortizacao 
VAR vResultado = 
    IF(
        vTipoContrato = "MENSAL" && vDataContexto >= vDataFimMesPrimeiroEmprestimo,
        vJurosAcumuladosTotal,
        IF(
            vTipoContrato <> "MENSAL" && vDataContexto >= vDataPrimeiroEmprestimo,
            vJurosAcumuladosTotal
        )
    )
RETURN
    vResultado

20230209_JUROS.pbix

Bruno Abdalla de Souza · Janeiro 31, 2023

@Bruno Santos, poderia explicar um pouco como seria este cálculo, por favor? Vi que na planilha já tem uma coluna de juros. Ela foi feita só para poder comprar com o POwer BI ou é para usar?
Se puder detalhar um pouco mais o que gostaria e qual o resultado desejado, ajudaria bastante.

Bruno Santos · Janeiro 31, 2023

Obrigado pelo retorno.

Fiz um ajuste na planilha em Excel.

Preciso de uma medida dax que realize o cálculo do saldo acumulado somando o juros, mas não estou conseguindo fazer com que o juros seja calculado sobre o saldo acumulado do dia anterior, e que logo em seguida o saldo acumulado some o juros.

Na guia Contratos deixei os títulos dos dados que serão retirados do sistema na cor vermelha e na cor azul são os dados que o power bi tem que trazer através das medidas e relacionamentos.

Na guia Contratos deixei as fórmulas dos cálculos como exemplo para facilitar o entendimento.

O saldo acumulado tem que ser sempre a soma do saldo anterior + o valor emprestado- amortização + juros.

O saldo anterior é a soma do valor do empréstimo, juros e a subtração da amortização.

O juros é calculado conforme os critérios da guia cadastros, nela tem a condição do percentual a ser considerado do CDI do dia anterior que fica na guia CDI, e também o período que o juros vai ocorrer "diário ou mensal".

Dados.xlsx

Bruno Abdalla de Souza · Janeiro 31, 2023

Veja se ajuda a dar uma pista pelo menos de fazer. Pode ser que tenha ajustes, mas o caminho é esse.

Juros Acumulados = 
VAR vContrato = MAX(CONTRATOS[CONTRATO])
VAR vTipoContrato = MAX(CADASTROS[Período])
VAR vPercCDI = MAX(CADASTROS[% CDI])
VAR vDataPrimeiroEmprestimo = 
    CALCULATE(
        MIN(DCalendario[Data]),
        ALL(DCalendario),
        FILTER(
            ALLSELECTED(CONTRATOS),
            CONTRATOS[VALOR] > 0
        )
    )
VAR vDataContexto = MAX(DCalendario[Data])
VAR vDataJuros = IF(vTipoContrato = "MENSAL", EOMONTH(vDataContexto, 0), vDataContexto)
VAR vJurosPrimeiraData = 
    IF(
        vDataContexto >= vDataPrimeiroEmprestimo,
        CALCULATE(
            SUM(CDI[CDI ACUMULADO]),
            DCalendario[Data] = vDataPrimeiroEmprestimo
        ) + 1
    )
VAR vJurosCDI = 
    IF(
        vDataContexto >= vDataPrimeiroEmprestimo,
        CALCULATE(
            SUM(CDI[CDI ACUMULADO]),
            FILTER(
                DCalendario,
                DCalendario[Data] = vDataJuros
            )
        ) + 1
    )
VAr vDifJuros = (vJurosCDI - vJurosPrimeiraData) * vPercCDI
VAR vSaldoAcumulado = 
    CALCULATE(
        [Empréstimo],
        FILTER(
            ALL(DCalendario[Data]), 
            DCalendario[Data] <= vDataJuros && 
            DCalendario[Data] >= vDataPrimeiroEmprestimo
        )
    )
VAR vAmortizacaoAcumulada = 
    CALCULATE(
        [Amortização],
        FILTER(
            ALL(DCalendario[Data]), 
            DCalendario[Data] < vDataJuros && 
            DCalendario[Data] >= vDataPrimeiroEmprestimo
        )
    )
VAR vJurosAcumulados = 
    IF(
        vDataContexto = vDataJuros,
    (vSaldoAcumulado - vAmortizacaoAcumulada) * vDifJuros
    )
RETURN
    vJurosAcumulados

20230131_JUROS.pbix

Bruno Santos · Fevereiro 1, 2023

Obrigado pelo retorno. Me ajudou a ter uma noção, mas não estou conseguindo fazer com que o juros fique igual a planilha.

Exemplo, no dia 12/05 o Juros seria de R$ 1.736,47, gerando um saldo acumulado no dia 13/05 de R$ 2.945.389,69.

No arquivo pbix anexo, o saldo ficou diferente da simulação no Excel, ficou R$ 2.944.197,19. É uma diferença de R$ 1.192,50.

20230201_JUROS.pbix Dados.xlsx

Bruno Abdalla de Souza · Fevereiro 1, 2023

Ok, vou olhar assim que possível. Talvez só amanhã.

Só uma dúvida. Na planilha você está usando como a informação de % contratado sobre a taxa CDI?

Um abração.

Editado Fevereiro 1, 2023 por Bruno Abdalla de Souza

Bruno Santos · Fevereiro 1, 2023

Bem observado. Fiz a correção no calculo da planilha. Obrigado.
Mas mesmo após o ajuste, continua apresentando diferença.
Considerar os seguintes arquivos.

20230201_JUROS.pbix Dados.xlsx

Bruno Abdalla de Souza · Fevereiro 2, 2023

Ainda não chegamos lá, mas está quase...

Juros Acumulados = 
VAR vContrato = MAX(CONTRATOS[CONTRATO])
VAR vTipoContrato = MAX(CADASTROS[Período])
VAR vPercCDI = MAX(CADASTROS[% CDI])
VAR vDataContexto = MAX(DCalendario[Data])
VAR vDataJuros = IF(vTipoContrato = "MENSAL", EOMONTH(vDataContexto, 0), vDataContexto)
VAR vDataPrimeiroEmprestimo = 
    CALCULATE(
        MIN(DCalendario[Data]),
        ALL(DCalendario),
        FILTER(
            ALLSELECTED(CONTRATOS),
            CONTRATOS[VALOR] > 0
        )
    )
VAR vJurosDataContexto = 
    CALCULATE(
        SUM(CDI[ACUMULADO DIÁRIO]),
        FILTER(
            DCalendario,
            DCalendario[Data] = vDataJuros
        )
    )
VAR vEmprestimos = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[VALOR] > 0,
        CONTRATOS[DATA] <= vDataJuros
    )
VAR vJurosAcumuladosEmprestimo = 
    SUMX(
        vEmprestimos,
        VAR vDataEmprestimo = CONTRATOS[DATA]
        VAR vJurosDataEmprestimo = 
            CALCULATE(
                SUM(CDI[ACUMULADO DIÁRIO]),
                DCalendario[Data] = vDataEmprestimo
            )
        VAR vDifJuros = (vJurosDataContexto - vJurosDataEmprestimo) * vPercCDI
        VAR vJurosAcumulados = CONTRATOS[VALOR] * vDifJuros
        RETURN
            vJurosAcumulados
    )
VAR vAmortizacoes = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[AMORTIZAÇÃO] > 0,
        CONTRATOS[DATA] <= vDataJuros
    )
VAR vJurosAcumuladosAmortizacao = 
    SUMX(
        vAmortizacoes,
        VAR vDataAmortizacao = CONTRATOS[DATA]
        VAR vJurosDataAmortizacao = 
            CALCULATE(
                SUM(CDI[ACUMULADO DIÁRIO]),
                DCalendario[Data] = vDataAmortizacao
            )
        VAR vDifJuros = (vJurosDataContexto - vJurosDataAmortizacao) * vPercCDI
        VAR vJurosAcumulados = CONTRATOS[AMORTIZAÇÃO] * vDifJuros
        RETURN
            vJurosAcumulados
    )
VAR vJurosAcumuladosTotal = vJurosAcumuladosEmprestimo - vJurosAcumuladosAmortizacao 
VAR vResultado = 
    IF(
        vDataContexto >= vDataPrimeiroEmprestimo && vDataContexto = vDataJuros,
        vJurosAcumuladosTotal
    )
RETURN
    vResultado

20230202_JUROS.pbix

Bruno Abdalla de Souza · Fevereiro 4, 2023

Estamos chegando. Falta algum ajuste na amortização...

Juros Acumulados v1 = 
VAR vContrato = MAX(CONTRATOS[CONTRATO])
VAR vTipoContrato = MAX(CADASTROS[Período])
VAR vPercCDI = MAX(CADASTROS[% CDI])
VAR vDataPrimeiroEmprestimo = 
    CALCULATE(
        MIN(DCalendario[Data]),
        ALL(DCalendario),
        FILTER(
            ALLSELECTED(CONTRATOS),
            CONTRATOS[VALOR] > 0
        )
    )
VAR vDataContexto = MAX(DCalendario[Data])
VAR vDataJuros = IF(vTipoContrato = "MENSAL", EOMONTH(vDataContexto, 0), vDataContexto)
VAR vEmprestimos = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[VALOR] > 0,
        CONTRATOS[DATA] <= vDataJuros
    )
VAR vJurosAcumuladosEmprestimo = 
    SUMX(
        vEmprestimos,
        VAR vDataEmprestimo = CONTRATOS[DATA]
        VAR vJuros = 
            PRODUCTX(
                FILTER(
                    ALL(DCalendario[Data]),
                    DCalendario[Data] <= vDataJuros && 
                    DCalendario[Data] >= vDataEmprestimo + 1
                ),
                (1 + CALCULATE(SUM(CDI[CDI DIA])) * vPercCDI)
            ) - 1
        VAR vJurosAcumulados = CONTRATOS[VALOR] * vJuros
        RETURN
            vJurosAcumulados
    )
VAR vAmortizacoes = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[AMORTIZAÇÃO] > 0,
        CONTRATOS[DATA] <= vDataJuros
    )
VAR vJurosAcumuladosAmortizacao = 
    SUMX(
        vAmortizacoes,
        VAR vDataAmortizacao = CONTRATOS[DATA]
        VAR vJuros = 
            PRODUCTX(
                FILTER(
                    ALL(DCalendario[Data]),
                    DCalendario[Data] <= vDataJuros && 
                    DCalendario[Data] >= vDataAmortizacao
                ),
                (1 + CALCULATE(SUM(CDI[CDI DIA])) * vPercCDI)
            ) - 1
        VAR vJurosAcumulados = CONTRATOS[AMORTIZAÇÃO] * vJuros
        RETURN
            vJurosAcumulados
    )
VAR vJurosAcumuladosTotal = vJurosAcumuladosEmprestimo - vJurosAcumuladosAmortizacao 
VAR vResultado = 
    IF(
        vDataContexto = vDataPrimeiroEmprestimo,
        0,
        IF(
            vDataContexto >= vDataPrimeiroEmprestimo && vDataContexto = vDataJuros,
            vJurosAcumuladosTotal
        )
    )
RETURN
    vResultado

20230204_JUROS.pbix

Bruno Abdalla de Souza · Fevereiro 4, 2023

@Bruno Santos, veja agora. Acho que deu certo.

Juros Acumulados v1 = 
VAR vContrato = MAX(CONTRATOS[CONTRATO])
VAR vTipoContrato = MAX(CADASTROS[Período])
VAR vPercCDI = MAX(CADASTROS[% CDI])
VAR vDataPrimeiroEmprestimo = 
    CALCULATE(
        MIN(DCalendario[Data]),
        ALL(DCalendario),
        FILTER(
            ALLSELECTED(CONTRATOS),
            CONTRATOS[VALOR] > 0
        )
    )
VAR vDataContexto = MAX(DCalendario[Data])
VAR vDataJuros = IF(vTipoContrato = "MENSAL", EOMONTH(vDataContexto, 0), vDataContexto)
VAR vEmprestimos = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[VALOR] > 0,
        CONTRATOS[DATA] <= vDataJuros
    )
VAR vJurosAcumuladosEmprestimo = 
    SUMX(
        vEmprestimos,
        VAR vDataEmprestimo = CONTRATOS[DATA]
        VAR vJuros = 
            IF(
                vDataJuros = vDataEmprestimo,
                0,
                PRODUCTX(
                    FILTER(
                        ALL(DCalendario[Data]),
                        DCalendario[Data] <= vDataJuros - IF(vDataEmprestimo = vDataPrimeiroEmprestimo, 1, 1) && 
                        DCalendario[Data] >= vDataEmprestimo + IF(vDataEmprestimo = vDataPrimeiroEmprestimo, 1, 0)
                    ),
                    VAR vJurosDia = CALCULATE(SUM(CDI[CDI DIA]))
                    RETURN
                        (1 + vJurosDia * IF(vJurosDia <> 0, vPercCDI, 1))
                ) - 1
            )
        VAR vJurosAcumulados = CONTRATOS[VALOR] * vJuros
        RETURN
            vJurosAcumulados
    )
VAR vAmortizacoes = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[AMORTIZAÇÃO] > 0,
        CONTRATOS[DATA] <= vDataJuros
    )
VAR vJurosAcumuladosAmortizacao = 
    SUMX(
        vAmortizacoes,
        VAR vDataAmortizacao = CONTRATOS[DATA]
        VAR vJuros = 
            IF(
                vDataJuros = vDataAmortizacao || vDataJuros = vDataAmortizacao + 1,
                0,
                PRODUCTX(
                    FILTER(
                        ALL(DCalendario[Data]),
                        DCalendario[Data] <= vDataJuros - 1 && 
                        DCalendario[Data] >= vDataAmortizacao + 1
                    ),
                    VAR vJurosDia = CALCULATE(SUM(CDI[CDI DIA]))
                    RETURN
                        (1 + vJurosDia * IF(vJurosDia <> 0, vPercCDI, 1))
                ) - 1
            )
        VAR vJurosAcumulados = CONTRATOS[AMORTIZAÇÃO] * vJuros
        RETURN
            vJurosAcumulados
    )
VAR vJurosAcumuladosTotal = vJurosAcumuladosEmprestimo - vJurosAcumuladosAmortizacao 
VAR vResultado = 
    IF(
        vDataContexto >= vDataPrimeiroEmprestimo && vDataContexto = vDataJuros,
        vJurosAcumuladosTotal
    )
RETURN
    vResultado

20230204_JUROS.pbix

Bruno Santos · Fevereiro 6, 2023

Obrigado pelo retorno e ajuda.

Os saldos dos contratos do tipo diário estão de acordo, mas no inicio do contrato o saldo acumulado do segundo dia ficou zerado e no juros acumulado aparece o valor do dia anterior como negativo.

Deveria aparecer assim:

No tipo de contrato com juros mensal no final do dia, apresentou diferença no saldo:

Na simulação ficaria assim:

Dados.xlsx

Bruno Abdalla de Souza · Fevereiro 6, 2023

Veja agora @Bruno Santos

Juros Acumulados v1 = 
VAR vContrato = MAX(CONTRATOS[CONTRATO])
VAR vTipoContrato = MAX(CADASTROS[Período])
VAR vPercCDI = MAX(CADASTROS[% CDI])
VAR vDataPrimeiroEmprestimo = 
    CALCULATE(
        MIN(DCalendario[Data]),
        ALL(DCalendario),
        FILTER(
            ALLSELECTED(CONTRATOS),
            CONTRATOS[VALOR] > 0
        )
    )
VAR vDataFimMesPrimeiroEmprestimo = EOMONTH(vDataPrimeiroEmprestimo, 0)
VAR vDataContexto = MAX(DCalendario[Data])
VAR vEmprestimos = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[VALOR] > 0,
        CONTRATOS[DATA] <= vDataContexto
    )
VAR vJurosAcumuladosEmprestimo = 
    SUMX(
        vEmprestimos,
        VAR vDataEmprestimo = CONTRATOS[DATA]
        VAR vJuros = 
            IF(
                vDataContexto = vDataEmprestimo,
                0,
                PRODUCTX(
                    FILTER(
                        ALL(DCalendario[Data]),
                        DCalendario[Data] <= vDataContexto && 
                        DCalendario[Data] >= vDataEmprestimo + 1
                    ),
                    VAR vJurosDia = IF(vTipoContrato = "MENSAL", CALCULATE(SUM(CDI[MÊS])), CALCULATE(SUM(CDI[CDI DIA])))
                    RETURN
                        (1 + vJurosDia * IF(vJurosDia <> 0, vPercCDI, 1))
                ) - 1
            )
        VAR vJurosAcumulados = CONTRATOS[VALOR] * vJuros
        RETURN
            vJurosAcumulados
    )
VAR vAmortizacoes = 
    CALCULATETABLE(
        ALLSELECTED(CONTRATOS),
        CONTRATOS[AMORTIZAÇÃO] > 0,
        CONTRATOS[DATA] <= vDataContexto
    )
VAR vJurosAcumuladosAmortizacao = 
    SUMX(
        vAmortizacoes,
        VAR vDataAmortizacao = CONTRATOS[DATA]
        VAR vJuros = 
            IF(
                vDataContexto = vDataAmortizacao || vDataContexto = vDataAmortizacao + 1,
                0,
                PRODUCTX(
                    FILTER(
                        ALL(DCalendario[Data]),
                        DCalendario[Data] <= vDataContexto && 
                        DCalendario[Data] >= vDataAmortizacao + 1
                    ),
                    VAR vJurosDia = IF(vTipoContrato = "MENSAL", CALCULATE(SUM(CDI[MÊS])), CALCULATE(SUM(CDI[CDI DIA])))
                    RETURN
                        (1 + vJurosDia * IF(vJurosDia <> 0, vPercCDI, 1))
                ) - 1
            )
        VAR vJurosAcumulados = CONTRATOS[AMORTIZAÇÃO] * vJuros
        RETURN
            vJurosAcumulados
    )
VAR vJurosAcumuladosTotal = vJurosAcumuladosEmprestimo - vJurosAcumuladosAmortizacao 
VAR vResultado = 
    IF(
        vTipoContrato = "MENSAL" && vDataContexto >= vDataFimMesPrimeiroEmprestimo,
        vJurosAcumuladosTotal,
        IF(
            vTipoContrato <> "MENSAL" && vDataContexto >= vDataPrimeiroEmprestimo,
            vJurosAcumuladosTotal
        )
    )
RETURN
    vResultado

20230204_JUROS.pbix

Bruno Santos · Fevereiro 7, 2023

@Bruno Abdalla de Souza, obrigado pelo retorno.

Os saldos finais estão batendo, mas em alguns casos aparece diferença no saldo diário.

No exemplo abaixo, o saldo do dia 13/05 deveria ficar ser R$ 2.945.421,98, e o Juros deveria ser R$ 1.407,61. No dia seguinte 14/05 aparece um valor negativo de Juros R$ - 155,39 que bate com a diferença.

Deveria aparecer assim:

20230207_JUROS.pbix

Entrar

Dax juros compostos diários e mensais por contrato de empréstimo

Pergunta

Link para o comentário

Compartilhar em outros sites

13 respostass a esta questão

Posts Recomendados

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Faça login para comentar