Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I've tried to use RATE function in DAX like in Excel, but without success. Some explanation about as I want to do:
I've the following data :
Could you help me to reproduce this function, please?
Thanks in advance,
Juan
Solved! Go to Solution.
Please follow these steps:
1. Pivot the table
2. Then change the data type of these values
3. Create a Measure, here is the DAX:
FIELD =
VAR _a =
SWITCH (
MAX ( [Periodicity] ),
"Monthly", 1,
"Quarterly", 3,
"Semiannual", 2,
12
)
VAR _b =
SWITCH (
MAX ( [Periodicity] ),
"Monthly", 12,
"Quarterly", 4,
"Semiannual", 2,
1
)
VAR _r =
RATE (
DIVIDE ( MAX ( [Duration] ), _a ),
MAX ( [Base] ) * MAX ( [COEFF] ),
- MAX ( [Base] ),
MAX ( [Residual Value] ),
MAX ( [Arrear ->0 / Advance -> 1] )
)
RETURN
IF ( ISBLANK ( MAX ( [COEFF] ) ), 0, _b * _r )
4. Change the format of the measure
5. Apply it to a card
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please follow these steps:
1. Pivot the table
2. Then change the data type of these values
3. Create a Measure, here is the DAX:
FIELD =
VAR _a =
SWITCH (
MAX ( [Periodicity] ),
"Monthly", 1,
"Quarterly", 3,
"Semiannual", 2,
12
)
VAR _b =
SWITCH (
MAX ( [Periodicity] ),
"Monthly", 12,
"Quarterly", 4,
"Semiannual", 2,
1
)
VAR _r =
RATE (
DIVIDE ( MAX ( [Duration] ), _a ),
MAX ( [Base] ) * MAX ( [COEFF] ),
- MAX ( [Base] ),
MAX ( [Residual Value] ),
MAX ( [Arrear ->0 / Advance -> 1] )
)
RETURN
IF ( ISBLANK ( MAX ( [COEFF] ) ), 0, _b * _r )
4. Change the format of the measure
5. Apply it to a card
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@juanmontes , is this not one financial function supported?