Issue with IFERROR Not Catching Errors Row by Row When Using YIELD in Power BI

[ElenaStone]

Hello,

I'm experiencing an issue with the YIELD function in Power BI. I'm trying to calculate the yield, but it seems that IFERROR is not catching errors on a row-by-row basis and assigning values when an error is detected.

Here are snapshots of my Excel and Power BI setup:

Excel Snapshot:

Power BI Snapshot:

The formula I'm using in Power BI is:

YIELD = YIELD(test[SETTLEMENT_DATE], test[MATURITY], test[COUPON], test[LAST_PRICE], test[PAR], test[Freq])

All columns seem to have the correct data types, but I still encounter #ERROR values. In Excel, I get #NUM! and some valid yield values as expected. However, in Power BI, I was expecting it to show results row-by-row and leave blank if there's an error, instead of showing an error for the whole column.

I tried wrapping the YIELD function with IFERROR, but it doesn't seem to handle the errors as expected. Is there any way to make Power BI show the result row-by-row and leave the cell blank if it encounters an error?

Attached the data here for reference

SETTLEMENT_DATE	MATURITY	COUPON	LAST_PRICE	PAR	Freq
07/30/2024	07/31/2024	11.00%	9	100	4
07/30/2024	08/31/2024	9.40%	94	100	4
07/30/2024	08/31/2024	9.40%	95	100	4

Thank you!

[BeaBF · Accepted solution]

@ElenaStone the formula does not recognizes that Settlement_Date is previous than Maturity, infact, with this code, the column returns the correct output:

Test_Yield =

var validsettle = IF(ISERROR(DATEVALUE(test[SETTLEMENT_DATE])), BLANK(), DATEVALUE(test[SETTLEMENT_DATE]))

var validmaturity = IF(ISERROR(DATEVALUE(test[MATURITY])), BLANK(), DATEVALUE(test[MATURITY]))

RETURN  

    YIELD(

      DATE(YEAR(validsettle), MONTH(validsettle), 01),

      validmaturity,

        test[COUPON], test[LAST_PRICE], test[PAR], test[Freq])

i've put 01 instead of 30 as day of settlement_date.

We'll retain the dynamic nature of SETTLEMENT_DATE but handle its day value to avoid errors: 

YIELD_WITH_ERROR_HANDLING =

VAR ValidSettlementDate = IF(ISERROR(DATEVALUE(test[SETTLEMENT_DATE])), BLANK(), DATEVALUE(test[SETTLEMENT_DATE]))

VAR ValidMaturityDate = IF(ISERROR(DATEVALUE(test[MATURITY])), BLANK(), DATEVALUE(test[MATURITY]))

VAR AdjustedSettlementDate = DATE(YEAR(ValidSettlementDate), MONTH(ValidSettlementDate), 1)  -- Adjust day to 1

VAR ValidCoupon = IF(ISERROR(VALUE(test[COUPON])), BLANK(), VALUE(test[COUPON]))

VAR ValidLastPrice = IF(ISERROR(VALUE(test[LAST_PRICE])), BLANK(), VALUE(test[LAST_PRICE]))

VAR ValidPar = IF(ISERROR(VALUE(test[PAR])), BLANK(), VALUE(test[PAR]))

VAR ValidFreq = IF(ISERROR(VALUE(test[Freq])), BLANK(), VALUE(test[Freq]))

RETURN

    IF(

        ISBLANK(ValidSettlementDate) ||

        ISBLANK(ValidMaturityDate) ||

        ISBLANK(ValidCoupon) ||

        ISBLANK(ValidLastPrice) ||

        ISBLANK(ValidPar) ||

        ISBLANK(ValidFreq),

        BLANK(),

        IFERROR(

            YIELD(

                AdjustedSettlementDate,

                ValidMaturityDate,

                ValidCoupon,

                ValidLastPrice,

                ValidPar,

                ValidFreq

            ),

            BLANK()

        )

    )

BBF