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,
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
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 |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
73 | |
47 | |
45 | |
17 | |
17 |