Forum Discussion

juanmontes's avatar
juanmontes
Frequent Visitor
4 years ago
Solved

Translate excel RATE function to DAX

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's avatar
    v-jianboli-msft
    4 years ago

    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.

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    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.