Excel Power Pivot DAX formula for interest payable on partial loan pre-payment

  • Durum: Closed
  • Ödül: $50
  • Alınan Girdiler: 3
  • Kazanan: treshakhanom

Yarışma Özeti

I need a DAX calculated column of the data model behind the attached Excel file that will reproduce the calculation of the yellow column of the spreadsheet data. If you open the data model with Power Pivot there will be a calculated column with 1's, and that is where the data should appear. You can use intermediate measures and columns to get to the result. Please post pictures of the data model measures and calculated columns, the contest is sealed so no-one else will see your entry. No macros please.

This result is needed to calculate the interest that becomes payable when the principal of a loan is partially repaid, where:

Payable interest = (Total interest accrued to date - Any amount already payable) x Repaid amount / Outstanding amount before repayment

The problem arises when there are a series of repayments, and you need to keep track of the amount already payable. I am using Power Pivot DAX and I get a circular reference error when trying to subtract the sum of the amounts already payable.

You will need Excel 2013 and PowerPivot add-in as well as Power View probably

Happy to provide more information if needed

Aranan Beceriler

Bu yarışmadan başlıca girdiler

Daha Fazla Girdi Görüntüleyin

Genel Açıklama Panosu

  • treshakhanom
    treshakhanom
    • 7 yıl önce

    Hello sir you there

    • 7 yıl önce
  • SrinivasaBagade
    SrinivasaBagade
    • 8 yıl önce

    Hi Please clarify that using power pivot is mandatory or I can use intermediatory sheet and derive the desire result with simple excel formula?

    • 8 yıl önce
    1. Gianburrasca
      Yarışma Sahibi
      • 8 yıl önce

      Yes, Power Pivot only please.

      • 8 yıl önce
  • Gianburrasca
    Yarışma Sahibi
    • 8 yıl önce

    Thank you for the question, yes it should be in Power Pivot. I need to use the data for other calculations and I want to avoid manual links etc.

    • 8 yıl önce
  • SrinivasaBagade
    SrinivasaBagade
    • 8 yıl önce

    Hi Please clarify that using power pivot is mandatory or I can use intermediatory sheet and derive the desire result with simple excel formula

    • 8 yıl önce
  • treshakhanom
    treshakhanom
    • 8 yıl önce

    Dear sir i am waiting your response,already i send you sample file

    • 8 yıl önce
    1. sajidmahmud
      sajidmahmud
      • 8 yıl önce

      Sir you send me one email because that is public place other guyes copy my file,so i can give you sample file personal way

      • 8 yıl önce
    2. Gianburrasca
      Yarışma Sahibi
      • 8 yıl önce

      The contest now has sealed bids, no-one else will see your proposed solution

      • 8 yıl önce
  • Gianburrasca
    Yarışma Sahibi
    • 8 yıl önce

    ok

    • 8 yıl önce
    1. sajidmahmud
      sajidmahmud
      • 8 yıl önce

      https://www.freelancer.com/u/sajidmahmud.html

      • 8 yıl önce
    2. sajidmahmud
      sajidmahmud
      • 8 yıl önce

      Sir i am wait for your project and ready so start job

      • 8 yıl önce
  • sajidmahmud
    sajidmahmud
    • 8 yıl önce

    Sir i send you image file please if you give me chance i can make final file

    • 8 yıl önce

Daha fazla yorum göster

Yarışmalara nasıl başlanır

  • Projenizi ilan edin

    Yarışmanızı İlan Edin Hızlı ve kolay

  • Tonlarca girdi alın

    Tonlarca Girdi Alın Bütün dünyadan

  • En iyi girdiyi seçin

    En iyi girdiyi seçin Dosyaları indirin - Kolay!

Şimdi bir Yarışma İlan Et ya da Bugün Bize Katılın!