Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |