Forum Discussion

wi11iamr's avatar
wi11iamr
Advocate II
9 years ago
Solved

Calculate compound increased fee from initial start date (Power Query)

Hi there,   Having the below sample dataset within a query, I have a monthly transaction record for each Contract reference number, along with the contract's initial fee for the fiirst year, and th...
Need Help
  • Sean's avatar
    Sean
    9 years ago
    Calculated Fee = 
'Table'[InitialFee]
    * POWER (
        1 + 'Table'[ContractAnnualEscalation],
        DATEDIFF (
            CALCULATE (
                FIRSTDATE ( 'Table'[ContractStartDate] ),
                ALLEXCEPT ( 'Table', 'Table'[ContractRef#] )
            ),
            'Table'[TransactionDate],
            YEAR
        )
    )

    I had several versions of this - it seems this is the simplest so far!

    Hope this helps! :smileyhappy:

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago
The separate table approach could be helpful if the interest rate changes. Although you can still handle this with a single table using iterators.
Sean's avatar
Sean
Community Champion
9 years ago

Even if you create another table the formula remains basically the same :smileyhappy: