Excel function DURATION to DAX

[PAULO]

How can I using DAX to calculate the DURATION function equal to that of Excel. DURATION(Settlement, Maturity, Coupon, Yld, Frequency).

[dax · Accepted solution]

Hi PAULO,

As  I know, there is no direct function to calculate duration in Powerbi, one workaround is that you could calculate this in Excel, then import these data in powerbi. Or I  think you need to create calculated column based on its function logic. You could refer to Bond duration(step by step example), bring the fields into the formula

For Example:

Measure 12 =
 ( 1 * 0.05 / ( 1 + 0.05 ) + 2 * 1.05 / ( 1 + 0.05 ) * ( 1 + 0.05 ) ) / ( 0.05 / ( 1 + 0.05 ) + 1.05 / ( 1 + 0.05 ) * ( 1 + 0.05 ) )

Because there is no direct function in powerbi, so if your store year* freq >5, it will more complex, you need to write long expression. So I suggest you calculate this in Excel then import in powerbi

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.