Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
103 | |
102 | |
87 | |
73 | |
67 |
User | Count |
---|---|
119 | |
111 | |
95 | |
79 | |
72 |