topic Re: Translate excel RATE function to DAX in DAX Commands and Tips

Translate excel RATE function to DAX

juanmontes — Tue, 28 Jun 2022 10:38:34 GMT

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

Re: Translate excel RATE function to DAX

amitchandak — Tue, 28 Jun 2022 12:00:04 GMT

juanmontes , is this not one financial function supported?

https://www.youtube.com/watch?v=9VYqbj2h4zE

https://www.youtube.com/watch?v=6oaSdjZ_FLQ

Re: Translate excel RATE function to DAX

v-jianboli-msft — Fri, 01 Jul 2022 09:36:44 GMT

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.