Forum Discussion

ElenaStone's avatar
ElenaStone
New Member
2 years ago
Solved

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

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_DATEMATURITYCOUPONLAST_PRICEPARFreq
07/30/202407/31/202411.00%91004
07/30/202408/31/20249.40%941004
07/30/202408/31/20249.40%951004

 

Thank you!

" Error Handling"
"DAX "
yield
  • BeaBF's avatar
    BeaBF
    2 years ago

    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

1 Reply

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    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