Forum Discussion
Solved
Translate excel RATE function to DAX
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 ...
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.