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:

Sean's avatar
Sean
Community Champion
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:

wi11iamr's avatar
wi11iamr
Advocate II
9 years ago

Thanks Sean - I truly appreciate the effort you took in testing different scenarios. Your suggested code works like a charm!