Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
https://www.youtube.com/watch?v=9VYqbj2h4zE
https://www.youtube.com/watch?v=6oaSdjZ_FLQ
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |