Formula for calculation while referencing a date field

[NCPowerBI]

Hi all,

I am trying to modify a column formula based on a new calculation that started 4/1/2023. Below is the current formula which will calculate the depreciation at 5% but will start calculating at 15% (NPER(0.15/12) as of 4/1/2023. So essentially I need to have everything before 4/1/2023 at NPER(0.05/12) and everything after at (NPER(0.15/12) and the date field I need to reference for this is: Lease_St_Dt_Chg_Prc_Eff_Dt and it's currently in short date format with all of the days listed (see below). Let me know if you know how to modify this to get the different parameters corrected!

Current Formula: New Term = IFERROR(ROUNDUP(NPER(0.05/12, 'Extension Data Query'[Payment],'Extension Data Query'[Org_RV]*-1,0,1),0),0)

[v-yanjiang-msft · Accepted solution]

Hi @NCPowerBI ,

According to your description, we need to check whether the date in the 'Lease_St_Dt_Chg_Prc_Eff_Dt' column is greater than or equal to April 1, 2023. If it is, the formula uses the NPER function with a rate of 15% to calculate the New Term. Otherwise, it uses the NPER function with a rate of 5%. If so, modify the formula to:

New Term =
IFERROR (
    IF (
        'Extension Data Query'[Lease_St_Dt_Chg_Prc_Eff_Dt] >= DATE ( 2023, 4, 1 ),
        ROUNDUP (
            NPER (
                0.15 / 12,
                'Extension Data Query'[Payment],
                'Extension Data Query'[Org_RV] * -1,
                0,
                1
            ),
            0
        ),
        ROUNDUP (
            NPER (
                0.05 / 12,
                'Extension Data Query'[Payment],
                'Extension Data Query'[Org_RV] * -1,
                0,
                1
            ),
            0
        )
    ),
    0
)

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.