Translate excel RATE function to DAX

[juanmontes]

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

[v-jianboli-msft · Accepted 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.

[amitchandak]

@juanmontes , is this not one financial function supported?

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

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