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 valu...
" 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

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